Delete the heading and trailing space at the beginning and at the end with a formula in an excel report

It is to delete the space at the beginning and/or at the end of a word and phrase, so not the spaces between. For the macro version, read Delete the heading and trailing space at the beginning and at the end using a macro in an excel report.

formula excel

 

When I use the formula ?

In many times, when I extract data into excel, cells with word have an empty space before or after the word and I need to delete it for other formulas referencing those cells to work.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=TRIM()

=CLEAN()

=SUBSTITUTE()

The first formula should work:

=TRIM(B2)

If it doesn’t work, so I use the combination:

=TRIM(CLEAN(SUBSTITUTE(B3,CHAR(160)," ")))

Interesting Topics