Excel 2000

[Sandra via OfficeKB.com]

I’ve received a spreadsheet that has the addresses in one cell, separated by
commas. I’ve used Text to columns > Delimited > Comma to split these into
four different cells. The problem is that at the beginning of the three new
cells there is a space before the text. Is there any way to delete all these
spaces other than going into each cell and deleting?

Thanks.

 

[Gary''s Student]

If you are not concerned about internal spaces, just use Find/Replace to
remove them.

 

[Gord Dibben]

Sandra

You could use the worksheet function TRIM in 3 helper columns by entering
=TRIM(cellref) and dragging across and down then pasting as values and deleting
the original 3 columns but the fastest way would be with a macro run on all 3
columns at once.

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub


Gord Dibben MS Excel MVP

 

[sandralong2 via OfficeKB.com]

Thank you so much.

Sandra

You could use the worksheet function TRIM in 3 helper columns by entering
=TRIM(cellref) and dragging across and down then pasting as values and deleting
the original 3 columns but the fastest way would be with a macro run on all 3
columns at once.

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben MS Excel MVP

IÂ’ve received a spreadsheet that has the addresses in one cell, separated by
commas. IÂ’ve used Text to columns > Delimited > Comma to split these into

[quoted text clipped - 3 lines]

Thanks.