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

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

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

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)

"Larry C" wrote in message
...
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

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

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 wrote:

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

--

Dave Peterson

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

Thanks Guys

Both ideas are huge help

Larry C


"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson