If you’ve ever been frustrated by extra spaces lurking in your Excel data, you’re not alone. Whether you’re copying data from another source or working with large datasets, unwanted spaces can wreak havoc on your calculations and analyses. Fortunately, Excel’s TRIM function is here to save the day! In this week’s YouTube video, we’ll explore the basics of the TRIM function, and then we’ll look at how it can be used to fix common issues with VLOOKUP, XLOOKUP and FILTER functions.
What is the TRIM Function?
The TRIM function in Excel is a simple yet powerful tool that removes all extra spaces from a text string, leaving just a single space between words. It’s particularly useful when dealing with imported data that might have inconsistent spacing. Here’s the syntax:
=TRIM(Text)
Simply replace “text” with the cell reference or text string you want to clean up. TRIM will then remove all leading and trailing spaces, as well as any extra spaces between words, leaving your data neat and tidy.
In the screenshot above, the formula in G2 is looking up the phone number that is in G1. It is looking it up in column A. The phone number exists in A6 however the lookup formula in G2 returns #N/A which indicates that the phone number in G2 does not exist in column A. So what’s going on?
G1 contains 01122 344556
A6 contains 01122 344556 <space>
In Excel’s mind these 2 values are different hence no match.
Using the TRIM function to remove the leading and trailing spaces (i.e. the ones at the start and end) from each value in column A will fix the problem.
Alternatively you can use the TRIM function inside the VLOOKUP or XLOOKUP function. Both methods are covered in the video together with an example of how to combine the TRIM function with the FILTER function.
Formulas used in the demo:
=TRIM(A2)
=TRIM(A2:A31)
=VLOOKUP(G1,TRIM(A2:D31),3,FALSE)
=FILTER(A2:C31,TRIM(C2:C31)=”iPhone”)
You can download a copy of the sample file by clicking through to YouTube. The link is in the YouTube description.