PROBLEM: needing to remove unwanted spaces in Excel data. Doing it manually will suck up most of your day!
SOLUTION: get to know the Trim function and save yourself time (and headaches).
Excel – removing extra spaces using The TRIM function
Needing to remove unwanted spaces from cells can be a challenge. I recently ran a workshop for a law firm. One of the members of their Business Development team explained to me that he had spent a whole day removing spaces from in front of the names of guests they were wanting to mail merge invitations to. When I showed him this function he used a word I can’t type here 🙂
Extra spaces can also cause a VLOOKUP to return the #N/A error. Just one extra space at the end of the data you are looking up in the table array makes it different from your lookup value. Running the TRIM function over the lookup value or the data in the table array is an excellent way to ensure both sets of data are the same.
Cleaning up unwanted spaces is so easy using the TRIM function.
In the example below the data in column A has been imported from an external data source. The data in column B is the result of using the formula shown in column C. As you can see, the TRIM function has removed ALL unnecessary spacing. It will spacing in front of the data, extra spaces in between the data, e.g. Robert[space][space]Brown, and any extra spaces at the end of the data.
I love this function! It saves a HUGE amount of time. It’s especially useful when using the Text to Columns feature as sometimes this can create an unwanted space in front of data as it is distributed to individual columns.
If I needed to now replace the formulas in column B with the result I would select from B2 to B4, copy the data and then right-click and select Paste Values. This will then paste the names into the cells and remove the formula. I would then delete column A as it is not good for anything.
If you found this post helpful please ‘Like’ us!
If you enjoyed this post you may also enjoy:
- Using VLOOKUP to compare two lists
- Hiding errors using the IFERROR function
- Finding and removing duplicate data in Excel