Ive received a spreadsheet that has the addresses in one cell, separated by
commas. Ive 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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200701/1

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

On Fri, 12 Jan 2007 22:53:31 GMT, "Sandra via OfficeKB.com" <u5657@uwe wrote:

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.

Thank you so much.

Gord Dibben wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200701/1

If you are not concerned about internal spaces, just use Find/Replace to
remove them.
--
Gary''s Student
gsnu200701


"Sandra via OfficeKB.com" wrote:

Ive received a spreadsheet that has the addresses in one cell, separated by
commas. Ive 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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200701/1