Question about parsing a column of cells with a comma in text

[Larry C]

Hello,

I have a column of cells that some of the cells have data like the following

124 Main Street, Suite 250

In a perfect world I would like to take the information after the comma and
move it to a cell next to it.

Then delete the comma.

Just posting on a remote chance that someone has done this before.

Thanks

Larry C

 

[Chip Pearson]

Try some code like the following. Select the cells you want to change and
then run the code.


Sub SplitAddress()
Dim Rng As Range
Dim Arr As Variant
For Each Rng In Selection.Cells
If Rng.Text <> vbNullString Then
Arr = Split(Rng.Text, ",")
Rng.Value = Arr(LBound(Arr))
If UBound(Arr) > LBound(Arr) Then
Rng(1, 2).Value = Arr(LBound(Arr) + 1)
End If
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dave Peterson]

Try selecting the column of addresses.
Then Data|Text to columns
(in the xl2003 menu system)
Delimited (by commas)
and finish up the wizard.

If you need a macro, record one when you do it manually.

 

[Larry C]

Thanks Guys

Both ideas are huge help

Larry C

Try selecting the column of addresses.
Then Data|Text to columns
(in the xl2003 menu system)
Delimited (by commas)
and finish up the wizard.

If you need a macro, record one when you do it manually.