Excel has some fantastic tools for dealing with large amounts of data and finding ways to somewhat “normalize” the date. If you’ve worked with data from a variety of sources before, one of the problems you probably have run into is data with spaces at the beginning or the end of the string. This may cause problems when dealing with the data and you’d like to find a way to automatically remove spaces at the beginning or end of the string.
Fortunately, Excel has a formula which removes white space! The formula TRIM removes all whitespace from a string – except a single space between words. Let’s look at some data and then see how to fix the extra whitespace problem:
Our data doesn’t look like there’s a problem until you attempt to combine it with the second column. We want a version that says “STATE NAME – ABBR,” such as ALABAMA – AL. Once we combine the data, we see the problem:
Incidentally, you can see the formula we used to combine the columns: =A2&” – “&B2. You can see in our list that the state name has some extra spaces which need to be fixed. Here’s how we fix it, let’s use TRIM in the formula:
Once we trim the state name, the list is corrected:
