In some situations, you may need to replace a part of a text string with some other text. For example, you may import data that contains asterisks, and you need to convert the asterisks to some other character. You could use Excel’s Find and Replace dialog box to make the replacement. If you prefer a formula-based solution, you can take advantage of either of two functions:
-
SUBSTITUTE: Replaces specific text in a string. Use this function when you know the character(s) to be replaced, but not the position.
-
REPLACE: Replaces text that occurs in a specific location within a string. Use this function when you know the position of the text to be replaced, but not the actual text.
The following formula uses the SUBSTITUTE function to replace 2006 with 2007 in the string 2006 Budget. The formula returns 2007 Budget.
=SUBSTITUTE("2006 Budget","2006","2007")
The following formula uses the SUBSTITUTE function to remove all spaces from a string. In other words, it replaces all space characters with an empty string. The formula returns the title of an excellent Liz Phair CD: Whitechocolatespaceegg.
=SUBSTITUTE("White chocolate space egg"," ","")
The following formula uses the REPLACE function to replace one character beginning at position 5 with nothing. In other words, it removes the fifth character (a hyphen) and returns Part544.
=REPLACE("Part-544",5,1,"")
You can, of course, nest these functions to perform multiple replacements in a single formula. The formula that follows demonstrates the power of nested SUBSTITUTE functions. The formula essentially strips out any of the following seven characters in cell A1: space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1," ",""),"-",""),":",""),"*",""),"_",""),"(",""),")","")
Therefore, if cell A1 contains the string Part-2A – Z(4M1)_A*, the formula returns Part2AZ4M1A.
Popularity: 6% [?]
Recent Comments