How to Trim Values Without Formula in Excel: Just One Click!

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):

Trim example
Example of trimming text.

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.

Example for the TRIM function in Excel.
Example for the TRIM function in Excel.

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).

Trim cell with Professor Excel Tools.
Trim cell with Professor Excel Tools.

Professor Excel Tools Box

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.

Insert TRIM with PowerQuery.
Insert TRIM with PowerQuery.

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.

Use the Find and Replace window to replace double spaces with single spaces.
Use the Find and Replace window to replace double spaces with single spaces.
  1. Open “Find and Replace” by pressing Ctrl + H on the keyboard.
  2. For “Find what”, type double spaces ” ” (without the quotation marks)
  3. For “Replace with”, type a single space character ” ” (also without the quotation marks).
  4. 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.

  1. Select your cells in your Excel sheet,
  2. copy and paste the following line of code into the immediate window and
  3. press Enter on the keyboard.
For Each cell In Selection: cell.Value = Trim(cell.Value): Next

The 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

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *