Chapter 11: Using Formulas to Manipulate Text
11
If you take a look at this formula closely, you can see that it’s made up of two parts that
are joined by the ampersand.
The first part uses Excel’s LEFT function.
UPPER(LEFT(C4,1))The LEFT function allows you to extract a given number of characters from the left of
a given text string. The LEFT function requires two arguments: the text string you are
evaluating and the number of characters you need to be extracted from the left of the text
string. In this case, we are extracting the left one character from the text in cell C4. We are
then making it uppercase by wrapping it in the UPPER function.
The second part is a bit trickier. Here we are using Excel’s RIGHT function:
LOWER(RIGHT(C4,LEN(C4)-1))Like the LEFT function, the RIGHT function requires two arguments: the text you are
evaluating and the number of characters you need to be extracted from the right of the
text string. In this case, however, we can’t just give the RIGHT function a hard-coded
number for the second argument. We have to calculate that number by subtracting 1 from
the entire length of the text string. We subtract 1 to account for the first character, which
is already uppercase thanks to the first part of the formula.
The LEN function is used to get the entire length of the text string. We subtract 1 from
that, and we have the number of characters needed for our RIGHT function.
We can finally pass all of that to the LOWER function to make everything but the first char-
acter lowercase.
Joining the two parts together gives us our sentence case:
=UPPER(LEFT(C4,1))&LOWER(RIGHT(C4,LEN(C4)-1))Removing spaces from a text string
If you pull data in from external databases and legacy systems, you will no doubt encoun-
ter text that contains extra spaces. Sometimes these extra spaces are found at the begin-
ning of the text, at the end of the text, or even between text strings (as in cell B6 shown
in Figure 11.4).
Extra spaces are generally evil because they can cause problems in lookup formulas, chart-
ing, column sizing, and printing.
Figure 11.4 illustrates how you can remove superfluous spaces by using the TRIM function.