Hello everyone,

Some times we need to remove the leading, trailing spaces or space in between the text in an entire column of data, one can might think to go manually but what if you have large set of data, the manual process will be take too much time.

So to do this quickly you can use below commands

For leading and trailing space you can use TRIM function

=TRIM(B1)

In the above code B1 denotes column cell from which we need to trim the space form starting or ending of the data, but this formula will not work if you need to remove any space in between the data.

for example: Find Nerd should be written as FindNerd

For this you can use SUBSTITUTE function.

like as below

=SUBSTITUTE(A1, " ", "")

See below screenshot:

In above screen-shot at the top you can clearly see the formula =SUBSTITUTE(A1, " ", "") and in A1 The text include trailing and leading space along with space in center of the text, so using the formula it removes all the spaces. This clearly states that using SUBSTITUTE function is better if you need to remove extra space from ever point else if you need to remove only leading or trailing space you can go with  TRIM function.

Note: As Substitute means in place of or instead of, so if you want to provide any other text in pace of space or similar text, you can use the same command.