Chapter 11: Creating Formulas That Manipulate Text
243
For example, assume that cell A1 contains the text Annual Profit Figures. The following for-
mula searches for the six-letter word Profit and replaces it 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 appear more complex than the examples in the preceding section. As
you can see, though, these examples can perform some very useful text manipulations. Space limi-
tations prevent a detailed explanation of how these formulas work, but this section gives you a
basic introduction.On the CD
You can access all the examples in this section on the companion CD-ROM. The file is named text formula
examples.xlsx.
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 works by using 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 uppercase and low-
ercase) in the string in cell A1. Using the UPPER function to convert the string makes this formula
work with both uppercase and lowercase characters:=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),”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 substring (contained in cell B1) within a string (contained in cell A1).
The substring can consist of any number of characters.=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)