Microsoft Office Tutorials and References

In Depth Information

Searching and replacing within a string

You can use the REPLACE function in conjunction with the SEARCH function to create a new string that re-

places part of the original text string with another string. In effect, you use the SEARCH function to find the

starting location used by the REPLACE function.

For example, assume cell A1 contains the text
Annual Profit Figures.
The following formula searches for the

word
Profit
and replaces those six characters with the word
Loss:

=REPLACE(A1,SEARCH(“Profit”,A1),6,”Loss”)

This next formula uses the SUBSTITUTE function to accomplish the same effect in a more efficient manner:

=SUBSTITUTE(A1,”Profit”,”Loss”)

Advanced Text Formulas

The examples in this section are more complex than the examples in the previous section, but as you'll see,

these formulas can perform some very useful text manipulations.

All of the examples in this section are available at this book's website. The filename is

text formula examples.xlsx file.

Counting specific characters in a cell

This formula counts the number of Bs (uppercase only) in the string in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,”B”,””))

This formula uses the SUBSTITUTE function to create a new string (in memory) that has all the Bs removed.

Then the length of this string is subtracted from the length of the original string. The result reveals the number

of Bs in the original string.

The following formula is a bit more versatile. It counts the number of Bs (both upper- and lowercase) in the

string in cell A1.

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,”B”,””),”b”,””))

Counting the occurrences of a substring in a cell

The formulas in the preceding section count the number of occurrences of a particular character in a string. The

following formula works with more than one character. It returns the number of occurrences of a particular sub-

string (contained in cell B1) within a string (contained in cell A1). The substring can consist of any number of

characters.