Your lookup (for example VLOOKUP) does not work? It cannot find results although it should? In such case, your values might have too many space characters: Leading space characters might still be visible, as well as double space characters in the middle of a text. But trailing spaces at the end of a text are very difficult to spot. In such case, trimming might help. Trim means in a nutshell that all unnecessary spaces are removed. Here is how to do that with five different ways.
Introduction: What does “trim” mean?
Trim means the following: You remove the following parameters from a text:
- Leading space characters. So, if a text starts with a blank / space character.
- Trailing space characters.
- Replace double space characters by single space characters.
Here is an example (space characters symbolized by dots):
Method 1: Trim with an Excel function
Excel provides a simple function for trimming. As you can guess – it’s called “=TRIM()”. It has just one argument: Your value to be trimmed.
Advantage of this method: It’s dynamic so that it adapts when values change or are updated.
Method 2: Trim values with just one click
The fastest method: Just select the cell and click on Trim on the Professor Excel ribbon. You can get the Professor Excel ribbon by installing Professor Excel Tools (click here for starting the download).
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Method 3: Trimming in PowerQuery
If you use PowerQuery, you can also remove “unnecessary” space characters there. If not, just skip to the next method below.
In the PowerQuery editor, select the respective columns. Then, go to Transform, click on Format and then on Trim. That’s it, PowerQuery inserts a new step.
Method 4: Manual trimming
Trimming text values manually is not very easy. There is basically one simple thing you can do: Remove double spaces. Leading and trailing spaces cannot be removed with simple methods (you have to use one of the other methods introduced here).
So, how to remove double spaces? Use the “Find and Replace” window.
- Open “Find and Replace” by pressing Ctrl + H on the keyboard.
- For “Find what”, type double spaces ” ” (without the quotation marks)
- For “Replace with”, type a single space character ” ” (also without the quotation marks).
- Click on “Replace All”. Click on “Replace All” again until you receive a message “We couldn’t find anything to replace […]”.
Method 5: Use a VBA macro to trim values
You can also use VBA to trim text in Excel. The fastest way would be to open the VBA editor.
- Select your cells in your Excel sheet,
- copy and paste the following line of code into the immediate window and
- press Enter on the keyboard.
For Each cell In Selection: cell.Value = Trim(cell.Value): NextThe immediate window is below the code area (highlighted in yellow below):
Please note: The trim function in VBA only removes leading and trailing spaces. Double spaces are not removed.
Image by Erubiel Flores from Pixabay
