Microsoft Office Tutorials and References
In Depth Information
If cell A1 contained the text FIRST QUARTER, the formula would return First quarter.
Replacing text with other text
In some situations, you may need a formula to replace a part of a text string with some other
text. For example, you may import data that contains asterisks, and you may need to convert the
asterisks to some other character. You could use Excel’s Home
command to make the replacement. If you prefer a formula-based solution, you can take
advantage of either of two functions:
Find & Select
h SUBSTITUTE replaces specific text in a string. Use this function when you know the
character(s) that you want to replace but not the position.
h REPLACE replaces text that occurs in a specific location within a string. Use this function
when you know the position of the text that you want to replace but not the actual text.
The following formula uses the SUBSTITUTE function to replace 2010 with 2011 in the string 2010
Budget . The formula returns 2011 Budget .
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
=SUBSTITUTE(“2011 Operating Budget”,” “,””)
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 .
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.
Therefore, if cell A1 contains the string Part-2A - Z(4M1)_A*, the formula returns Part2AZ4M1A.