+ Reply to Thread
Results 1 to 8 of 8

Removing leading and trailing spaces

  1. #1
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Removing leading and trailing spaces

    Hi,
    I have used trim() to remove trailing and leading space but it removes space towards the right side of the string. It is not removing space of the left side of the string.

    I copied the data from my email i pasted in excel and it is giving me result as " 24 "
    i would like to see the result as 24 without any space.

    Could anyone please help me ?
    thank you

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Removing leading and trailing spaces

    you could try a LEN() function to make sure there are actually spaces....and according the help file it should remove all spaces except spaces between words on TEXT...HTH
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Removing leading and trailing spaces

    The macro at this website will remove all leading/trailing
    and multiple interspersed char 32 space characters.
    It will also remove and/or convert char 160 non breaking
    spaces into standard char 32 space characters. It will
    work on text or numbers and the numbers will be
    converted to true numeric numbers.

    I use this macro dozens of times every single day!
    It's a real time saver.

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    Last edited by Tony Valko; 03-15-2013 at 09:57 AM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Removing leading and trailing spaces

    Can you please help me with a LEN()?

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Removing leading and trailing spaces

    say you have your data in cell A1.....click in cell B1 and type =LEN(A1).....this will return a value of the length of a string(TEXT)....if your data is TEXT...

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: Removing leading and trailing spaces

    Hi

    There are several space characters. The one that Trim() removes is the ASCII space (character 32). When you copy text from the web you sometimes get another space, a html no-break space, character 160.

    Try:

    =TRIM(SUBSTITUTE(A1,CHAR(160)," "))

  7. #7
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Removing leading and trailing spaces

    thank you lecxe. Its working exactly the way i wanted.

  8. #8
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: Removing leading and trailing spaces

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1