TRIM function to remove extra spaces

TRIM function to remove extra spaces
Last Updated on 25/04/2024
Reading time: 2 minutes

The TRIM function is very useful for cleaning all the spaces before and after a string

  1. The TRIM function needs only one argument

    It's the text to clean

  2. Convert the formula into a value

    However, after applying the TRIM function to the cells, you must convert the formulas into values.

=TRIM(text)

When importing data into Excel, it is strongly recommended to use this function to 'clean' extra spaces in your cells. The function is very simple to use, requiring only one argument: your text to be cleaned.

In this example, sorting the data doesn't return a correct result because of the extra spaces.

Sort with Spaces returns wrong result

Remove the spaces with the TRIM function

So, we need to remove the spaces at the beginning of all the cells.

=TRIM(H2)

TRIM to remove the spaces in your cells

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

Transform the formulas into values

Of course, you can't retain the formulas because it implies that the original cells still contain blanks. To keep only the results, convert the formulas into values by using 'Paste Special' and selecting 'Values', as demonstrated in this video.

Alternatively, 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 on the mouse
  5. Select the option Copy here as Values only
Trick to Copy Paste value with the mouse

Leave a Reply

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

TRIM function to remove extra spaces

Reading time: 2 minutes
Last Updated on 25/04/2024

The TRIM function is very useful for cleaning all the spaces before and after a string

  1. The TRIM function needs only one argument

    It's the text to clean

  2. Convert the formula into a value

    However, after applying the TRIM function to the cells, you must convert the formulas into values.

=TRIM(text)

When importing data into Excel, it is strongly recommended to use this function to 'clean' extra spaces in your cells. The function is very simple to use, requiring only one argument: your text to be cleaned.

In this example, sorting the data doesn't return a correct result because of the extra spaces.

Sort with Spaces returns wrong result

Remove the spaces with the TRIM function

So, we need to remove the spaces at the beginning of all the cells.

=TRIM(H2)

TRIM to remove the spaces in your cells

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

Transform the formulas into values

Of course, you can't retain the formulas because it implies that the original cells still contain blanks. To keep only the results, convert the formulas into values by using 'Paste Special' and selecting 'Values', as demonstrated in this video.

Alternatively, 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 on the mouse
  5. Select the option Copy here as Values only
Trick to Copy Paste value with the mouse

Leave a Reply

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