Part II: Working with Formulas and Functions
Substituting text strings
There are cases when it’s helpful to substitute some text with other text. One such case is
where you encounter the annoying apostrophe S ('S) quirk that you get with the PROPER
function. To see what we mean, enter this formula into Excel:
=PROPER("STARBUCK'S COFFEE")
This formula is meant to convert the given text into title case (where the first letter of
every word is capitalized). The actual result of the formula is this:
Starbuck'S Coffee
Note how the PROPER function capitalizes the S after the apostrophe—annoying to say the
least.
However, with a little help from Excel’s SUBSTITUTE function, you can avoid this annoy-
ance. Figure 11.8 shows the fix using the following formula:
=SUBSTITUTE(PROPER(SUBSTITUTE(B4,"'","qzx")),"qzx","'")
FIGURE 11.8
Fixing the apostrophe S issue with the SUBSTITUTE function
Our formula uses the SUBSTITUTE function, which requires three arguments: the target
text, the old text you want to be replaced, and the new text to use as the replacement.
As you look at the full formula, you’ll note there are two SUBSTITUTE functions in use.
This formula is actually two formulas (one nested in the other). The first formula is the part
that reads as follows:
PROPER(SUBSTITUTE(B4,"'","qzx"))
In this part, we are using the SUBTITUTE function to replace the apostrophe (') with qzx.
This may seem like a crazy thing to do, but there is some method here. The PROPER func-
tion will essentially capitalize any letter coming directly after a symbol. Here, we are trick-
ing the PROPER function by substituting the apostrophe with a benign set of letters that
are unlikely to be strung together in the original text.
