Text to Columns doesn't work for this problem.
 

Im not a programmer so can someone help me with code for the problem listed below. Text to Columns doesnt work because some cells have data separated by three commas and others only two commas.

Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma and second comma (but not the third comma), individually into their own columns. I start with one column and end up with three columns. The problem I have is that there is sometimes an extra comma because some have addresses and suite no. and others just have address.

Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address, city state zip

If I use the text to columns some records have four columns and some three. So they don't fall in the correct column and have to be moved around. Also, if there is an address and suite no. they have to be put back together.

Thanks in advance.

Text to Columns doesn't work for this problem.
 

Select your data in column A and run the macro

Sub AAAA()
Dim sStr As String
Dim cell As Range
For Each cell In Selection
sStr = cell.Value
varr = Split(sStr, ",")
cell.Offset(0, 2) = Trim(varr(UBound(varr)))
cell.Offset(0, 1) = Trim(varr(UBound(varr) - 1))
If UBound(varr) = 3 Then
cell.Value = Trim(varr(0) & ", " & varr(1))
Else
cell.Value = Trim(varr(0))
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"Ann Vlna" wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below. Text to Columns doesn't work because some cells have data
separated by three commas and others only two commas.

Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma
and second comma (but not the third comma), individually into their own
columns. I start with one column and end up with three columns. The
problem I have is that there is sometimes an extra comma because some have
addresses and suite no. and others just have address.

Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address, city state zip

If I use the text to columns some records have four columns and some
three. So they don't fall in the correct column and have to be moved
around. Also, if there is an address and suite no. they have to be put back
together.

Thanks in advance.

Text to Columns doesn't work for this problem.
 

Tom, thank you so much for the code. It did exactly what I wanted it to. You have saved me many hours of work and I appreciate it.

"Tom Ogilvy" wrote:

Select your data in column A and run the macro

Sub AAAA()
Dim sStr As String
Dim cell As Range
For Each cell In Selection
sStr = cell.Value
varr = Split(sStr, ",")
cell.Offset(0, 2) = Trim(varr(UBound(varr)))
cell.Offset(0, 1) = Trim(varr(UBound(varr) - 1))
If UBound(varr) = 3 Then
cell.Value = Trim(varr(0) & ", " & varr(1))
Else
cell.Value = Trim(varr(0))
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"Ann Vlna" wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below. Text to Columns doesn't work because some cells have data
separated by three commas and others only two commas.

Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma
and second comma (but not the third comma), individually into their own
columns. I start with one column and end up with three columns. The
problem I have is that there is sometimes an extra comma because some have
addresses and suite no. and others just have address.

Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address, city state zip

If I use the text to columns some records have four columns and some
three. So they don't fall in the correct column and have to be moved
around. Also, if there is an address and suite no. they have to be put back
together.

Thanks in advance.