In this post you will learn how to substitute a text string with another text string in Excel.
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:
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:
Note how the PROPER function capitalizes the S after the apostrophe—annoying to say the
However, with a little help from Excel’s SUBSTITUTE function, you can avoid this annoy-
ance. Figure below shows the fix using the following formula:
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:
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.
The second formula actually wraps the first. This formula substitutes the benign qzx with
So, the entire formula replaces the apostrophe with qzx, performs the PROPER function,
and then reverts the qzx to an apostrophe.