#247 Cleaning Up Your (Excel) Act: Part 2

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..

Capitalization

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.

Unwanted Spaces

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.

=TRIM(G2)

Combining Cells

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

=E2&” “&C2

nancy hellung-larsen

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.

=PROPER(E2&” “&C2)

Nancy Hellung-Larsen

You can revisit a few more options for dealing with ‘messy’ text from previous tips here & here.

Advertisements

One thought on “#247 Cleaning Up Your (Excel) Act: Part 2

  1. Excellent tips on how to clean up your data! I work with spreadsheets on a daily basis that need cleaning up and think the TRIM function is one of the most useful features of Excel. So much so, I have written a macro that goes through a selection and trim each cell!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s