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.
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
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:
Similarly, if you want to correct all double blanks by one, you write the formula:
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 :.