Excel- find leading spaces

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thank you.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote