Excel 2019 Bible

Chapter 11: Using Formulas to Manipulate Text

11

The second formula actually wraps the first. This formula substitutes the benign qzx with

an apostrophe:

=SUBSTITUTE(PROPER(SUBSTITUTE(B4,"'","qzx")),"qzx","'")

So, the entire formula replaces the apostrophe with qzx, performs the PROPER function,

and then reverts the qzx to an apostrophe.

Counting specific characters in a cell

A useful trick is to be able to count the number of times a specific character exists in a text

string. The technique for doing this in Excel is relatively clever. If you wanted to count the

number of times the letter s appears in the word Mississippi, for example, you could count

them by hand, of course, but systematically, you could follow these general steps:

1. Measure the character length of the word Mississippi (11 characters).


2. Measure the character length after removing every letter s (7 characters).


3. Subtract the adjusted length from the original length.

After performing these steps, you’ll accurately conclude that the letter s occurs four times

in the word Mississippi.

A real-world use for this technique of counting specific characters is to calculate a word

count in Excel. In Figure 11.9, you’ll see the following formula used to count the number of

words entered in cell B4 (nine words in this case):

=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1

FIGURE 11.9

Calculating the number of words in a cell

This formula essentially follows the steps mentioned earlier in the setup for this section.

The formula first uses the LEN function to measure the length of the text in cell B4:

LEN(B4)

It then uses the SUBSTITUTE function to remove the spaces from the text:

SUBSTITUTE(B4," ","")