Formula Excel : Substituting Text Strings

In some situations, it’s helpful to substitute some text with other text. One such case is when 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("STAR'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 the following:

Star'S Coffee
Note how the PROPER function capitalizes the S after the apostrophe. Annoying, to say the least.

However, with a little help from the Excel’s SUBSTITUTE function, you can avoid this annoyance. Image 3-7 shows the fix using the following formula:

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

Image 3-7: Fixing the apostrophe S issue with the SUBSTITUTE function.

How it works

The formula uses the SUBSTITUTE function, which requires three arguments: the target text; the old text you want replaced; and the new text to use as the replacement. As you look at the full formula, note that it uses two SUBSTITUTE functions. This formula is actually two formulas (one nested in the other). The first formula is the part that reads.

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

In this part, you use the SUBSTITUTE function to replace the apostrophe (’) with qzx. This may seem like a crazy thing to do, but there is some method here. Essentially, the PROPER function capitalizes any letter coming directly after a symbol. You trick the PROPER function by substituting the apostrophe with a benign set of letters that are unlikely to be strung together in the original text.
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 back to an apostrophe.

Categories: Formula