Last week in Part 1 we saw how to clean up the display of ‘messy’ numbers in your spreadsheet. This week we’ll continue the cleansing theme and take a look at how fix your ugly text data. If you ever inherit data with problems such as incorrect capitalization and unwanted extra spaces, you’ll definitely want to grab a fresh cup of coffee and pull up a nice comfy chair for this weeks tip.
We’ll use this same ugly sample data from last week as out starting point..
If you want fix the capitalization without having to retype your anything you can use the =UPPER, =LOWER and =PROPER functions to have Excel do it for you.
It’s hard to see in the image above but a bunch of the text cells in our example have extra, unwanted spaces in them. Take a look at the address in row 2…nobody wants that kind of ugliness do they? Using Excel’s TRIM function makes quick work of this problem by removing all spaces from text except for single spaces between words. Using =TRIM(G2) will clean this right up as you can see here.
In the past we’ve seen how to split data apart using the Text to Columns function. There are also times when you want to do just the opposite and combine two or more cells. The easiest way to do that is to use an &. From our sample data above, we could display the full name in a single cell by using this simple formula: =E2&” “&C2
The & symbol is the glue that holds things all together.
NOTE: You need to add the space inside quotes so the text doesn’t run together. i.e. nancyhellung-larsen
Putting It All Together
You can take this one step further and combine these as necessary to fix more than one problem at a time. For example, =PROPER(E2&” “&C2) gives us a nice, better looking version of what we started with.