Chapter 25: Importing and Cleaning Data
2525
FIGURE 25.16
Using Flash Fill to split names
Changing the case of text
Often, you’ll want to make text in a column consistent in terms of case. Excel provides no
direct way to change the case of text, but it’s easy to do with formulas. (See the sidebar
“Transforming Data with Formulas.”)
The three relevant functions are as follows:
UPPER: This converts the text to ALL UPPERCASE.
LOWER: This converts the text to all lowercase.
PROPER: This converts the text to Proper Case. (The first letter in each word is capital-
ized, as in a proper name.)These functions are quite straightforward. They operate only on alphabetic characters and
just ignore all other characters and return them unchanged.
If you use the PROPER function, you’ll probably need to do some additional cleanup to
handle exceptions. The following are examples of transformations that you probably would
consider incorrect:
■ The letter following an apostrophe is always capitalized (for example, Don’T). This
is done, apparently, to handle names like O’Reilly.