↑ Return to Text functions

TRIM – SUBSTITUTE

 

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 :.

2 comments

  1. Chris from California

    Your trainings are great. They are easy to follow, easy to understand and capture the essence of the functions beautifully. Much thanks for your time spent to show others the power of some of these functions and formulas.

  2. seo machine pro guide

    It’s remarkable to go to see this website and reading the views of all friends on the topic of this paragraph, while I am also keen of getting familiarity.

Leave a Reply

%d bloggers like this: