Part IV: Managing and Analyzing Data
■ (^) The PROPER function doesn’t handle names with an embedded capital letter, such
as McDonald.
■ (^) “Minor” words such as and and the are always capitalized. For example, some people
would prefer that the third word in United States Of America not be capitalized.
You can correct some of these problems by using Find and Replace.
Transforming Data with Formulas
Many of the data cleanup examples in this chapter describe how to use formulas and functions to trans-
form data in some way. For example, you can use the UPPER function to transform text into uppercase.
When the data is transformed, you’ll have two columns: the original data and the transformed data.
Almost always, you’ll want to replace the original data with the transformed data. Here’s how to do it:
- Insert a new temporary column for formulas to transform the original data.
- Create your formulas in the temporary column, and make sure that the formulas do
what they were intended to do. - Select the formula cells.
- Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C).
- Select the original data cells.
- Choose Home ➪ Clipboard ➪ Paste ➪ Values (V).
This procedure replaces the original data with the transformed data. Then you can delete the tempo-
rary column that holds the formulas.Removing extra spaces
It’s usually a good idea to ensure that data doesn’t have extra spaces. It’s difficult to spot
a space character at the end of a text string. Extra spaces can cause lots of problems, espe-
cially when you need to compare text strings. The text July is not the same as the text July
with a space appended to the end. The first is four characters long, and the second is five
characters long.The TRIM function removes all leading and trailing spaces and replaces interior multiple
spaces with a single space. This formula uses the TRIM function. The formula returns
Fourth Quarter Earnings (with no excess spaces):
=TRIM(" Fourth Quarter Earnings ")Data that is imported from a web page often contains a different type of space: a nonbreak-
ing space, indicated by in HTML code. In Excel, this character can be generated by
this formula:
=CHAR(160)