Remove annoying spaces with trim()

Introduction

You’ve got some data that has annoying spaces either before or after the actual values. This may happen because you’ve loaded a fixed-width text file that uses spaces to pad each column, or it may happen because the underlying data is simply messy. Either way, we can fix this quickly and easily using trim() to remove the unnecessary spaces.

1. Identify the problem

In our example, we have a list of total revenue by source code for a recent DM acquisition campaign. Each source code corresponds to a specific list select from a rented list. For the most part, everything looks normal, but we can already see one problem in the raw data: what’s going on with row 4?

It looks like there are some spaces before “Save the Birds and Trees” for source code P25MG125, because the text doesn’t align with the rows above it. That’s a bummer but not ruinous. However, we start to see more problems if we try to use a pivot table to summarize revenue by List Name:

We’ve got a big problem now. Revenue isn’t being summarized by list name - each list is repeated just like it was in the source data. This defeats the purpose of our pivot table. This is happening because some of these list names contain spaces before/after them. The screenshot below is taken from a text editor set up to replace all spaces with dots so that we can see the problem more clearly:

Because of these spaces, Excel thinks each of these list names are distinct. Let’s remove the spaces so we can work with clean data.

2. Remove spaces using trim()

Our approach will be to use a helper column and Excel’s trim() function to remove the unwanted spaces. First, insert a new column to the right of the list names. You can call this column whatever you’d like, we will use “Corrected List Name” in our example:

Next, in cell C2, enter the formula =trim(B2). This formula tells Excel, “Take the data that’s in cell B2 (List Name) and remove all the spaces from the front and back leaving only the spaces in the middle.” Then fill that formula down for all of your rows:

3. Clean up

Now you’ve got a column that shows the corrected list names. If we refresh our pivot table and use Corrected List Name, we will see the desired results:

This may be enough for your work and you can stop here. But often, you will need to deliver this data to someone else for their use and you don’t want there to be any confusion about what “Corrected List Name” is or which list name column to use. And you certainly don’t want them to be able to accidentally break the formulas you’ve inserted into column C.

To fix all these issues, we will copy the data from Corrected List Name and paste them as values (meaning Excel will paste the result of each formula into the target cell rather than the formula itself) into our List Name column. Then we can delete our Corrected List Name helper column:

And that’s it! We have recreated our raw data with cleaned values for List Name.