Text to Columns doesn't work for this problem.

[Guest]

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

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.

 

[Tom Ogilvy]

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

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

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.

 

[Guest]

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.