Excel Trim

John Lilleystone • January 21, 2021

Use the TRIM function in Excel to remove whitespaces

Sometimes you get data which contains additional invisible whitespaces at either the front of end of a cell. These can cause problems when trying to perform other functions on the data such as VLOOKUP for example. Excel has a built in function called TRIM which will remove them for you.

Someone sent me some information the other day and I needed to a VLOOKUP on the data they sent me against some data. However despite having the right formula it returned an error. On closer inspection, it turned out that the data supplied to me had invisible spaces at the start and end of the product code which caused my VLOOKUP to fail. It was quite a lot of data and I didn’t want to have to manually tidy up each cell as it would’ve taken me hours. TRIM to the rescue.

Here’s an example – I want to pull in the descriptions from my product list on the left into my sales table on the right. A quick VLOOKUP should sort that out in a jiffy – however I get #N/A even though the part numbers in both tables appear to match. If you look closely though you’ll see that there’s a cheeky little space before the product code on the left table.

So we need to remove the spaces and there’s an Excel function called TRIM which will do just that.

Insert a new column and enter =TRIM(B2) and press enter. Copy the formula down to the end of your dataset. Then Copy the values in this new column and Paste-Special over the original values in column B.New Paragraph

Boom! VLOOKUP now works and descriptions get pulled in.

< Older Post

Share

Mail