The Replace Function in Excel

Standard

Occasionally in data sets, you need to replace text in a certain column or row with another string of text.  You can use the Replace function to change the text in only the cells needed.  The Replace function syntax is =REPLACE(old_text, start_num, num_chars, new_text).  In this example, our old text will be “wlee” in the Approver ID column and we will be replacing it with “kthimyan”.

 

table

 

Since we want to replace the whole text string of “wlee”, the start number (start_num) will be 1 – beginning with “w”.  There are 7 characters in “kthimyan”.  We will finish the function with “kthimyan”.  Remember that when entering text in an Excel formula, you will need to surround the text string with quotes.  The formula will look like the example below.

 

Replace formula

 

Drag the formula down the desired column.  All will be updated with the desired text.

 

Completed table

 

If you want to change the ending of the text string, you can modify the formula.  In the example below we turn “wlee” into “wlong”.  As you will see in the formula, we being the replacement with the 3rd letter, “e”.  There are 3 replacement letters – “ong”.

 

Change text

 

 

Related posts: