Cleaning Data using Excel's TRIM Function
When working in Excel, sometimes the data in a worksheet is “dirty” and contains leading or trailing spaces. This, of course, leads to issues when trying to match the data to other entries using functions such as VLOOKUP or HLOOKUP. Making matters worse, it’s not easy to spot which cells contain data that has leading or trailing spaces, exacerbating the process of “cleaning” the data. Fortunately, Excel’s TRIM function provides an easy-to-use option to quickly remove leading and trailing spaces from your data, and in this tip, you will learn how you can put this feature to work to quickly correct these issues.
Understanding How to Work with TRIM
To understand the problem and how to solve it using Excel’s TRIM function, consider the dataset pictured in Figure 1. Although the data was sorted alphabetically, it appears to be out of order. This is because the first four entries – Apples, Edamame, Peaches, and Potatoes – have leading spaces in front of the text. Further, while not evident from the screen capture, some of the first four entries and some of the last four entries have trailing spaces after the text. Not only is the sorting of the data affected by the extraneous leading spaces, but any form of attempting to match the data in column B to data in other cells would also be affected by the leading and/or the trailing spaces.
To solve the issues outlined above, we merely need to enter a formula that contains the TRIM function to the worksheet. The TRIM function is specifically designed to remove all leading and trailing spaces from an entry in Excel. Thus, by entering =TRIM(B2) into cell D2, as shown in Figure 2, we can quickly strip away all leading and trailing spaces from the entry. Further, because relative cell referencing is used, we can copy the formulas quickly to correct all of the entries in the worksheet, as shown in Figure 3.
Summary
Simple. Quick. Efficient. Excel’s very easy-to-use TRIM function makes cleaning up your data a breeze, so that sorting, matching, and other processes are not affected by the presence of leading or trailing spaces. Don’t rekey “dirty” data – use TRIM to clean it for you!
For a video demonstration of this tip, please enjoy the video below.
ARE YOU RECEIVING THE K2 TECH UPDATE NEWSLETTER
BY EMAIL EVERY MONTH?
Sign up now so you don’t miss the next issue.