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
One 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
Another 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:
- Type a 1 in any cell. You’ll be deleting this later, so it doesn’t matter where it goes.
- Copy the 1 with Ctrl+C or the Copy button in the ribbon.
- Highlight the column of numbers stored as text.
- Click the Paste drop down arrow, or right click anywhere in your highlighted column.
- Choose Paste Special.
- In the Paste Special dialog box select Multiply.
- 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
If 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!
IAAP Edge 