Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

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.

You can access all the examples in this section on the companion CD-ROM in the
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