Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Remove leading or trailing spaces in an entire column of data?

    •  
    • 10/10
    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 984
    Comment on it

    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.

     

    How to Remove leading or trailing spaces in an entire column of data?

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: