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.
Search JabSto ::




Custom Search