Recently, I imported some data into Excel. Some of the entries looked similar to this:
There was random spacing in the names. With rows and rows of entries, it’s unimaginable to go through and delete all those spaces manually.
How can you quickly delete these spaces with minimally effort?
Using Excel’s TRIM function.
The TRIM function will remove all spaces from text except for single spaces between words.
Believe it or not, in my years as an IT trainer, I have seen people manually go through all their entries and delete those spaces.
Using the TRIM function is the quick and easy way.
If you look closely at the image above, you see there are leading spaces, spaces in between the first and last name and possibly some trailing spaces.
In cell B2, I will enter the formula
I then copy the formula down to cell B14.
Excellent – now I have a column with names and the spaces removed.
But this corrected column is a formula.
What I want is to get rid of this original column and keep the column with the trimmed data. But what happens if I delete this original data column? My trimmed data is gone.
Here’s what I can do.
Copy the trimmed data in column B and paste as values only.
Select the data in Column B and copy. Then in Column C, right-click and under paste options, select the Values option.
Now, I can delete columns A & B.
Imagine if you had hundreds of rows of data that needed cleaning up? This often happens when you import data from another sources. But you just learned how to quickly clean the data up.
A few notes about the TRIM Function:
- TRIM will remove extra spaces from text. Thus, it will leave only single spaces between words and no space characters at the start or end of the text.
- It is very useful when cleaning up text from other applications or environments.
- TRIM only removes the ASCII space character (32) from the text.
- The Unicode text often contains a non-breaking space character (160) that appears in web pages as an HTML entity. It will not be removed with TRIM.
Check out the video on my YouTube channel: Quickly Remove Extra Spaces