Microsoft Office Tutorials and References
In Depth Information
2. Create your formulas in the temporary column and make sure that the formulas do what they were intended to
3. Select the formula cells.
4. Choose Home⇒Clipboard⇒Copy (or press Ctrl+C).
5. Select the original data cells.
6. Choose Home⇒Clipboard⇒Paste⇒Values (V).
This procedure replaces the original data with the transformed data. Then you can delete the temporary column
that holds the formulas.
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 clean-up to handle exceptions.
Here are some examples of transformations that you'd probably consider incorrect:
• The letter following an apostrophe is always capitalized (for example, Don'T). This is done, apparently, to
handle names like O'Reilly.
• 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 would prefer that the
fourth word in United States Of America not be capitalized.
Often, you can correct some of these problems by using Find and Replace.
Removing extra spaces
It's usually a good idea to ensure that data doesn't have extra spaces. It's impossible to spot a space character at
the end of a text string. Extra spaces can cause lots of problems, especially 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 charac-
ters long, and the second is five characters long.
Create a formula that uses the TRIM function to remove all leading and trailing spaces, and also replace mul-
tiple spaces with a single space. This example uses the TRIM function. The formula returns Fourth Quarter
Earnings (with no excess spaces):
Data that is imported from a web page often contains a different type of space: a nonbreaking space, indicated
by   in HTML code. In Excel, this character can be generated by this formula:
You can use a formula like this to replace those spaces with normal spaces:
Or use this formula to replace the nonbreaking space character with normal spaces and also remove excess