Microsoft Office Tutorials and References

In Depth Information

**Text Functions**

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

Replace

command to make the replacement. If you prefer a formula-based solution, you can take

advantage of either of two functions:

➜

Editing

➜

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
.

=SUBSTITUTE(“2010 Budget”,”2010”,”2011”)

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

2011OperatingBudget
.

=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
.

=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.