Results 1 to 10 of 10
  1. #1
    bwithey
    Guest

    Excel- find leading spaces

    Would anyone know how to find cells with leading spaces and then remove them? A co-worker has over 500 rows of items that need to be sorted, unfortunately about 350 of the cells appear to have leading spaces before the entries so the sort will not work properly. Is there a "relatively" simple way to find and replace these spaces? We're using XL 2K with Win NT and 98. Thanks for your help.

  2. #2
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel- find leading spaces

    Try TRIM()
    "Removes all spaces from text except for single spaces between words."
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Cleveland, Ohio, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel- find leading spaces

    <font face="Comic Sans MS"><font color=blue>Excel has a TRIM function in EXCEL that will remove all spaces except the spaces between words. =TRIM(A1) would be the syntax. If you put this function in a blank column and then filled down to include all 500 rows then it would take out the leading spaces. You could then either hide the original column or copy the new entries and then use a Paste Special to paste the values back in the original spots. I hope this helps!</font face=comic></font color=blue>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel- find leading spaces

    Highlight all the cells you want to deal with and simply goto Edit, Replace and in the "Find What" box enter a space and leave the "Replace with" box as is and click on Replace All. (Excel 97)

    The Trim() Function can be used in formulas to remove blank spaces.

    Andrew

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel- find leading spaces

    Sorry but I meant to point out that the Edit Relpace method would be unsuitable for cells containing text of more than one word.

    Andrew

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel- find leading spaces

    Here is a simple macro that goes through all the cells in the range "R", which is here defined Range("A1").currentregion. It is clear that you can use
    Set R = Range("A1:A10")
    instead of the range definition in the code below if you only want to screen the cells A1 till A10.

    Sub GetRidofLeadingSpaces()
    Dim R As Range
    Dim cel As Range
    Set R = Range("A1").CurrentRegion
    For Each cel In R
    cel.Value = Trim(cel.Value)
    Next
    End Sub

  7. #7
    bwithey
    Guest

    Re: Excel- find leading spaces

    Thank you.

  8. #8
    bwithey
    Guest

    Re: Excel- find leading spaces

    Thank you even more for the extra info. It worked beautifully and I learned something!

  9. #9
    bwithey
    Guest

    Re: Excel- find leading spaces

    I tried Find/Replace, but first selected the column. This worked as well. Thanks!

  10. #10
    bwithey
    Guest

    Re: Excel- find leading spaces

    I can record macros, but have not successfully written one. I'll play with this and keep trying. Thanks!

Posting Permissions

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