Home » Text functions » Remove spaces in Excel with the TRIM function

Remove spaces in Excel with the TRIM function

Reading time: 2 minutes
Last Updated on 28/12/2022 by Frédéric LE GUEN

The TRIM function

The TRIM function is very useful for cleaning all the spaces before and after a string. This is really important to avoid mistakes or a wrong analyse like in this example.

Sort with Spaces

When you import data, it is strongly recommended to use this function to "clean" the blank characters in your cells.

The function is very simple to use. There is only one argument, your text to clean

=TRIM(your text)

Remove the spaces with the TRIM function

In this example, we need to remove the spaces a the beginning of the string.

So we must clean strings with the TRIM function to remove the blank spaces.

=TRIM(H2)

Function TRIM to remove spaces

The spaces inside the string are not removed. Only the spaces at the beginning or at the end of the string are removed.

Transform the formulas in values

Of course, you can't keep the formulas because it means that the original cells still have the blank.

To keep the result "clean", convert the formulas in value by making a paste special VALUE like in this video.

Or there is also the trick with the mouse

  1. Select your range of cells
  2. Move the range with the right button of the mouse
  3. Return to the previous location
  4. Release the button of the mouse
  5. Select the option Copy here as Values only
Copy Paste value with the mouse

Leave a Reply

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

Remove spaces in Excel with the TRIM function

Reading time: 2 minutes
Last Updated on 28/12/2022 by Frédéric LE GUEN

The TRIM function

The TRIM function is very useful for cleaning all the spaces before and after a string. This is really important to avoid mistakes or a wrong analyse like in this example.

Sort with Spaces

When you import data, it is strongly recommended to use this function to "clean" the blank characters in your cells.

The function is very simple to use. There is only one argument, your text to clean

=TRIM(your text)

Remove the spaces with the TRIM function

In this example, we need to remove the spaces a the beginning of the string.

So we must clean strings with the TRIM function to remove the blank spaces.

=TRIM(H2)

Function TRIM to remove spaces

The spaces inside the string are not removed. Only the spaces at the beginning or at the end of the string are removed.

Transform the formulas in values

Of course, you can't keep the formulas because it means that the original cells still have the blank.

To keep the result "clean", convert the formulas in value by making a paste special VALUE like in this video.

Or there is also the trick with the mouse

  1. Select your range of cells
  2. Move the range with the right button of the mouse
  3. Return to the previous location
  4. Release the button of the mouse
  5. Select the option Copy here as Values only
Copy Paste value with the mouse

Leave a Reply

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