Closed Thread
Results 1 to 13 of 13

Remove leading space

  1. #1
    Karidre
    Guest

    Remove leading space

    I wonder if someone can help me with this...it's driving me crazy.

    I need to delete a leading space from multiple cells, 200 or more. The
    leading space is in front of text that is often more than one word so I can't
    just copy into Word and do a find and replace on the spaces.

    I looked in many places for help and found out about the Trim function but
    when I tried it, it didn't work for me. Am I doing it wrong? I created a
    column next to the column that I want to remove the leading spaces from. then
    I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
    it did was put the exact same thing as before (with the leading space still
    in it) in that column?

    Any suggestions? Thanks!

    - Kari

  2. #2
    JMB
    Guest

    RE: Remove leading space

    The trim function should have worked if it was just a space entered w/the
    spacebar.

    It could be a special character that appears as a space. Try
    Search/Replace. In the search line, hold down the Alt key and hit 255 on the
    number keypad. Then hit Replace All.

    Be sure to back up your workbook.




    "Karidre" wrote:

    > I wonder if someone can help me with this...it's driving me crazy.
    >
    > I need to delete a leading space from multiple cells, 200 or more. The
    > leading space is in front of text that is often more than one word so I can't
    > just copy into Word and do a find and replace on the spaces.
    >
    > I looked in many places for help and found out about the Trim function but
    > when I tried it, it didn't work for me. Am I doing it wrong? I created a
    > column next to the column that I want to remove the leading spaces from. then
    > I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
    > it did was put the exact same thing as before (with the leading space still
    > in it) in that column?
    >
    > Any suggestions? Thanks!
    >
    > - Kari


  3. #3
    Biff
    Guest

    Re: Remove leading space

    Hi!

    Maybe those leading spaces aren't your standard leading spaces, char 32!
    They may be some other unseen character like the dreaded char 160.

    Try finding out what the actual characters are. Use this formula in a
    temporary helper column:

    =CODE(B2)

    Once you know what those characters are then they should be fairly easy to
    get rid of using either Edit>Replace or, maybe a formula like:

    =MID(B2,2,255)

    Or:

    =SUBSTITUTE(B2,LEFT(B2),"")

    Then you could convert the formulas to constants by doing a Copy>Paste
    Special>Values. Then deleting the original data.

    Biff

    "Karidre" <[email protected]> wrote in message
    news:[email protected]...
    >I wonder if someone can help me with this...it's driving me crazy.
    >
    > I need to delete a leading space from multiple cells, 200 or more. The
    > leading space is in front of text that is often more than one word so I
    > can't
    > just copy into Word and do a find and replace on the spaces.
    >
    > I looked in many places for help and found out about the Trim function but
    > when I tried it, it didn't work for me. Am I doing it wrong? I created a
    > column next to the column that I want to remove the leading spaces from.
    > then
    > I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But
    > all
    > it did was put the exact same thing as before (with the leading space
    > still
    > in it) in that column?
    >
    > Any suggestions? Thanks!
    >
    > - Kari




  4. #4
    Paul Skinner
    Guest

    Re: Remove leading space

    On 24/10/05 2:03 PM, in article
    [email protected], "Karidre"
    <[email protected]> wrote:

    > I wonder if someone can help me with this...it's driving me crazy.
    >
    > I need to delete a leading space from multiple cells, 200 or more. The
    > leading space is in front of text that is often more than one word so I can't
    > just copy into Word and do a find and replace on the spaces.
    >
    > I looked in many places for help and found out about the Trim function but
    > when I tried it, it didn't work for me. Am I doing it wrong? I created a
    > column next to the column that I want to remove the leading spaces from. then
    > I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
    > it did was put the exact same thing as before (with the leading space still
    > in it) in that column?
    >
    > Any suggestions? Thanks!
    >
    > - Kari

    Try :- Insert an empty column on the right of your Data, select the column
    you want to fix. Go to top menu- Data, Text to column, This will show you a
    preview of the text You wish to fix. Click next, Click the checkbox space,
    Click next, click finish. Done. Then You can copy data back to where you
    want it.
    Paul


  5. #5
    Dave Peterson
    Guest

    Re: Remove leading space

    If you're getting this data from a web page, you may have non-breaking spaces
    (HTML stuff) in those cells.

    You may want to try David McRitchie's routine to clean that stuff up:

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    (look for "Sub Trimall()")

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    =========
    If you want to try a formula, this may work:

    =trim(substitute(a1,char(160)," "))

    It changes the non-Breaking character to a space, then trims the value.

    Excel also has =clean() that you may want to review, too.

    Karidre wrote:
    >
    > I wonder if someone can help me with this...it's driving me crazy.
    >
    > I need to delete a leading space from multiple cells, 200 or more. The
    > leading space is in front of text that is often more than one word so I can't
    > just copy into Word and do a find and replace on the spaces.
    >
    > I looked in many places for help and found out about the Trim function but
    > when I tried it, it didn't work for me. Am I doing it wrong? I created a
    > column next to the column that I want to remove the leading spaces from. then
    > I put =TRIM(B2) in the first cell, =TRIM (B3) in the next one, etc. But all
    > it did was put the exact same thing as before (with the leading space still
    > in it) in that column?
    >
    > Any suggestions? Thanks!
    >
    > - Kari


    --

    Dave Peterson

  6. #6
    Registered User
    Join Date
    06-25-2012
    Location
    Tauranga, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Remove leading space

    Quote Originally Posted by Biff View Post
    Hi!

    Maybe those leading spaces aren't your standard leading spaces, char 32!
    They may be some other unseen character like the dreaded char 160.

    Try finding out what the actual characters are. Use this formula in a
    temporary helper column:

    =CODE(B2)
    Thanks Biff, I was trying to use the clean function for a similar problem, but couldn't get a result until I used the CODE() function to find out which character type was used in my data. Much appreciated
    Last edited by arlu1201; 08-09-2012 at 03:55 AM.

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Remove leading space

    Thanks Dave

    I've the same problem with french phone number format i.e. "_04 94 96 49 05". when I use this formula =trim(substitute(a1,char(160)," ")).
    It's amazing !
    It works... problem solved.

    thanks again, Dave.

  8. #8
    Registered User
    Join Date
    05-21-2014
    Posts
    2

    Red face Re: Remove leading space

    Another contribution from an Excel bodger.

    Save Your Worksheet in case of problems.
    Then Select one cell with the leading Space.
    Go to the Formula Bar at the top of the worksheet.
    Select and copy the leading space showing there by holding down the left mouse button and using Ctrl C.
    Then select all the cells with the leading space.
    Use Ctrl F/Replace and Ctrl V to paste in the leading character (it probably won't be visible in the search field) to search for and replace the leading space in the selected cells.
    Just Use 'Replace' until you are sure that it works OK then Use 'Replace All'.

    Check the formatting of the altered cells to suit your intentions.

    Thanks for all the other useful stuff that others proposed

  9. #9
    Registered User
    Join Date
    05-07-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: Remove leading space

    Quote Originally Posted by otellabgaveldot View Post
    Another contribution from an Excel bodger.

    Save Your Worksheet in case of problems.
    Then Select one cell with the leading Space.
    Go to the Formula Bar at the top of the worksheet.
    Select and copy the leading space showing there by holding down the left mouse button and using Ctrl C.
    Then select all the cells with the leading space.
    Use Ctrl F/Replace and Ctrl V to paste in the leading character (it probably won't be visible in the search field) to search for and replace the leading space in the selected cells.
    Just Use 'Replace' until you are sure that it works OK then Use 'Replace All'.

    Check the formatting of the altered cells to suit your intentions.

    Thanks for all the other useful stuff that others proposed
    I had the same problem and came across this solution. It was very helpful. Thank you

  10. #10
    Registered User
    Join Date
    05-21-2014
    Posts
    2

    Smile Re: Remove leading space

    tgallag1
    Glad to help.

    otellabgaveldot

  11. #11
    Registered User
    Join Date
    04-19-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Remove leading space

    hay man try this

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

  12. #12
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    127

    Re: Remove leading space

    Hi all,

    Can someone please help me how can I use formula in data validation so that user can not enter leading space in front of the text.
    I need a error alert pop up kind of notification & thus I need help.

    Regards,
    Vikas

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Remove leading space

    kisanvikas2015,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)
    Last edited by protonLeah; 06-05-2015 at 12:27 AM.
    Ben Van Johnson

Closed 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