These days the data we work with can come from a wide variety of of sources. Often we’ve exported it from another system or downloaded it from an online source which is great, until you get data that doesn’t give you accurate results. One cause for this is that all data is not created equal. Sometimes the data you get isn’t as clean as we need it to be. For example, one common challenge is data containing extra spaces.
Take a close look at the data above. I want to count how many of each item I have listed and the results are not right. Can you see why?
In this case, it is because some of my data contains extra spaces (which I can’t see just by looking) causing me to see inaccurate results. That’s not good but this is easy to fix in Excel using the TRIM function. The TRIM function will automatically remove the extra spaces and give you the accurate results you need.
This function is really straight forward and looks like this:
=TRIM(text to trim)
Whenever you are using this type of formula to clean up your data the process is generally something along these lines:
- Insert a new column (B) next to the original column (A) that needs cleaning.
- Add a formula that will transform the data at the top of the new column (B). In this case the TRIM function.
- Fill down the formula in the new column (B).
- Select the new column (B), copy it, and then paste as values over the original column (A). ( Paste Special…Values)
- Remove the newly created column you used to convert/clean up your data (B)
That’s it..quick and easy cleanup to eliminate one of the common problems with imported Excel data.
Tip of the Week #426