How to Use the REPLACE, REPT, RIGHT SEARCH and SUBSTITUTE Text Functions to Clean Data in Excel

By Stephen L. Nelson, E. C. Nelson

Importing data into Excel can be problematic. Issue you may have when you import data into Excel is that your text labels don’t look right. These text functions can help you keep your data clean.

The REPLACE function

The REPLACE function replaces a portion of a text string. The function uses the following syntax:

REPLACE(old_text,start_num,num_chars,new_text)

The old_text argument, which is case-sensitive, either supplies the text string from which you grab some text fragment or it references the cell holding the text string. The start_num argument, which is the starting position, tells Excel where the text starts that you want to replace.

The num_chars argument tells Excel the length of the text fragment (how many characters) that you want to replace. The new_text argument, also case-sensitive, tells Excel what new text you want to use to replace the old text. For example, to replace the name Chamberlain with the name Churchill in the text string Mr. Chamberlain, use the following formula:

REPLACE("Mr. Chamberlain",5,11,"Churchill")

The function returns the text string Mr. Churchill.

The REPT function

The REPT function repeats a text string. The function uses the following syntax:

REPT(text,number_times)

The text argument either supplies the text string or references the cell holding the text string. The number_times argument tells Excel how many times you want to repeat the text. For example, the following formula:

REPT("Walla",2")

returns the text string WallaWalla.

The RIGHT function

The RIGHT function returns a specified number of characters from the right end of a text string. The function uses the following syntax:

RIGHT(text,num_chars)

The text argument either supplies the text string that you want to manipulate or references the cell holding the text string. The num_chars argument tells Excel how many characters to grab.

For example, to grab the rightmost two characters from the text string Redmond WA, use the following formula:

RIGHT("Redmond WA",2)

The SEARCH function

The SEARCH function calculates the starting position of a text fragment within a text string. The function uses the following syntax:

SEARCH(find_text,within_text,start_num)

The find_text argument tells Excel what text fragment you’re looking for. The within_text argument tells Excel what text string that you want to search. The argument tells Excel where to start its search. The start_num argument is optional. If you leave it blank, Excel starts the search at beginning of the within_text string.

For example, to identify the position at which the text fragment Churchill starts in the text string Mr. Churchill, use the following formula:

SEARCH("Churchill","Mr. Churchill",1)

The SUBSTITUTE function

The SUBSTITUTE function replaces occurrences of text in a text string. The function uses the following syntax:

SUBSTITUTE(text,old_text,new_text,instances)

The text argument tells Excel what text string you want to edit by replacing some text fragment. The old_text argument identifies the to-be-replaced text fragment. The new_text supplies the new replacement text.

As an example of how the SUBSTITUTE function works, suppose that you need to replace the word Senator with the word President in the text string.

SUBSTITUTE("Senator Obama","Senator","President")

The instances argument is optional, but you can use it to tell Excel for which instance of old_text you want to make the substitution. For example, the function

SUBSTITUTE("Senator Senator","Senator","President",1)

returns the text string President Senator Obama.

The function

SUBSTITUTE("Senator Senator Obama","Senator","President",2)

returns the text string Senator President Obama.

If you leave the instances argument blank, Excel replaces each occurrence of the old_text with the new_text. For example, the function

SUBSTITUTE("Senator Senator Obama","Senator","President")

returns the text string Senator Senator Obama.