Excel Imports: 3 Tips for turning what you get into what you need

by Melissa Esquibel, MCT

We’re not getting any less data. That’s for sure! But, unfortunately, the quality of the data we’re getting isn’t improving at the same rate. Here are a 3 tips to turn the stuff you get into something you can use.

Banish Leading and Trailing Spaces

blog-pic1One of the tasks you may face is combining data from one set of records with another. Matching them up using find, VLookUp, or pivot tables can be problematic if extra spaces have crept into the data. Case in point. I was working with someone who was trying to compare customer records by first and last name. After concatenating or combining the first and last name (A1&B1), we found that some names were preceded by a space while others had spaces following the name. Still others had multiple spaces between the first and last name. Because of the volume of records, it wasn’t necessarily easy to spot.

We wrapped our A1&B1 formula in the Excel TRIM function, like this TRIM(A1&B1). TRIM gets rid of leading and trailing spaces. It also removes all but one space between words. TRIM is one of many text functions that you can use in Excel to tackle ugly imports.

Convert Numbers Stored as Text to Numbers

blog-pic2Another common issue when working in Excel with data from other systems is that numbers are sometimes brought in as text. Sometimes this is a non-issue, other times it can make more work for you. Here’s a time-honored quick way to change a whole column of numbers stored as text back into numbers or, as Excel calls them, values. Here are the steps:

  1. Type a 1 in any cell. You’ll be deleting this later, so it doesn’t matter where it goes.
  2. Copy the 1 with Ctrl+C or the Copy button in the ribbon.
  3. Highlight the column of numbers stored as text.
  4. Click the Paste drop down arrow, or right click anywhere in your highlighted column.
  5. Choose Paste Special.
  6. In the Paste Special dialog box select Multiply.
  7. Delete the 1. Check out your new values!

This method doesn’t not disturb any alpha data. So, it won’t change any column titles you have typed in the column. It also doesn’t change any numbers that were actually stored as numbers, because any number multiplied by 1 is still that same number.

Cherry Pick the Good Stuff with Flash Fill

blog-pic3If you’re running the newest versions of Excel (2013+), you have access to a cool new tool called Flash Fill. Let’s say your data import combines several data items into one cell. But, you need just the state in its own cell. In column B, type the first two-letter state code. Then in the next row down, type just the first letter of the next one. You’ll notice the remaining list of states pops up. Just press enter to allow Excel to complete your list. If you also needed the zip code in the next column, in C1 you would type 60625, then in C2, 3. All of the zips would now pop up. This is just 4 rows, imagine if you had 4,000!

tec17-logo-700wFor in-person training on this and many other topics, register for IAAP TEC17 today!

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 )

Facebook photo

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

Connecting to %s