• Excel- find leading spaces

    Author
    Topic
    #1768352

    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.

    Viewing 3 reply threads
    Author
    Replies
    • #1778984

      Try TRIM()
      “Removes all spaces from text except for single spaces between words.”

    • #1778985

      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!

      • #1778992

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

    • #1778987

      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

      • #1778988

        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

      • #1778993

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

    • #1778990

      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

      • #1778994

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

    Viewing 3 reply threads
    Reply To: Excel- find leading spaces

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: