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


The TRIM function

This function is very useful for cleaning up all the spaces before and after a string. This is very common when you copy/paste data from the Web.

The function works very simply to use. You just have to select the cell with your text to clean and that's it.

=TRIM(your text)

In this example, we have sorted the  values of the column A in the column B. But the sort doesn't respect the alphabetic order because the words DDDD and FFFF begin with a blank.

Similarly, after the words BBBB and GGGG you can see the borders. This is because of spaces after the last letter of these words.

So we will clean strings with the TRIM function to correct these defects. (Double click in the worksheet to see appearing against the formula).

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


The SUBSTITUTE function

As you can guest, the function substitutes (or replace) a character with another one in a cell. This function is rarely used because it is largely duplicate with the Replace dialog box.

But it can be very useful to remove multiple blanks inside a cell.

If you want to remove ALL spaces from a string, you write the following formula:


=SUBSTITUTE(A1," ","")

Similarly, if you want to correct all double blanks by one, you write the formula:


=SUBSTITUTE(A1,"  "," ")

But the most interesting is to specify precisely the part of the cell you want to replace. This is made possible by adding at the end of your formula (the fourth parameter) a number corresponding to the nth instance found. : |

It is not clear? Then this example will help you better understanding 😉

In the following cells, you have several athlete names with their time in the same cells. The symbol : is used both to separate the name of the athlete and as separators of the time. We have created 2 examples of the SUBSTITUTE functions with 2 different values for the fourth parameter. (Double click in the worksheet to display the formulas).

In the second column, we have substituted the first sign : Found in all those contained in the cells by the sign . In the third column, this time we replace only the second symbol :.

Related articles

Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/function-trim-substitute/

Leave a Reply

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