top of page

Replace

This function is a find/replace function with more precision. The find/replace function within Microsoft Excel will search for a text string anywhere in a cells contents.  However, there are times when users need to look only in certain areas.  

 

This function has three replace options.  It will look for a particular string:

  1. At the beginning of the string, i.e. prefix

  2. At the end of the string, i.e. suffix

  3. Or anywhere in the string.

There are also three range options.  It allows the user to find a string:

  1. Within selected cells

  2. Within a particular column(s)

  3. Anywhere in the table.

Consider the simple table of data.

Example Table - Replace before click

Range Option 1: Replace within a selection.

Step 1: Select the cells in which you would like to search for your string or term.

Example Table - Replace - Select Range

Step 2: Open the replace tool by clicking the replace function.
 
Populate the 'Find term' text box.  In this example, we are searching for the text string 'abc'. 

Select 'Replace Prefix'.

Pick 'Selected Cells'.



Click on 'Replace'

Replace Function Input Form

Result: Though the string 'abc' exists in the table, it was not found within the selection.  As such the table was not modified; nothing was replaced.

Example Table - After Replace - No hits

Try again by expanding the selection and running the replace tool with the same criteria as above.
 

Example Table - Replace - Before Click - Exanded Selection

Result: The string 'abc'  was found within the selection and removed. 

Example Table - Replace after removal of 'abc' from string

Range Option 2: Find and Replace a Prefix

Start with the original table with the following entries and selections: 

  • Enter 'abc' in the 'Find term' text box.

  • Enter 'XYZ' in the 'Replace with' text box. 

  • Select 'Replace Prefix'.

  • Select 'Columns of Selection'. 

Now, click the 'Replace' button.

Example Table - Prior to Replace - Limited selected range
Replace Input Form - Find 'abc' & replace with 'XYZ'

Result: The string 'abc'  was found within the selection and replaced with 'XYZ'. 

Example Table - After 'abc' was replaced with 'XYZ'

Range Option 3: Find and Replace a string anywhere in the table.

Start with the original table with the following entries and selections: 

  • Enter 'XYZ' in the 'Find term' text box.

  • Enter 'QQQ' in the 'Replace with' text box. 

  • Select 'Replace Anywhere term is found'

  • Select 'Anywhere in Entire Table'. 

Now, click the 'Replace' button.

Replace Input Form - Replace 'XYZ' with 'QQQ' anywhere in the table

Result: The string 'XYZ'  was found in two places within the table and replaced with 'QQQ' in both places. 

Example Table - After replacing 'XYZ' with 'QQQ'
bottom of page