Copy text after 3rd comma from the right in a cell

[derricksmith]

Hi,


I have an address the following formats in a cell:
Customer Name, 1 Address, 2 Address, City, ST, 00000-0000, US
Other Customer, 1 Address, City, ST, 12345, US


I need a formula to just extract the “ST” state information. My problem is sometimes there is no “2 Address” and corresponding comma. So using “Text to Columns” doesn’t create consistent data.


As you can see the number of commas can vary. How I extract from the “ST” from after the 3rd comma from the right?


Thanks!

 

[MrTeeny]

=right(a1,len(a1)-find("st, ",a1)-3)

 

[tonyyy]

derricksmith,

Welcome to the Board.

If you're comfortable with a vba approach, you might consider the following...

Sub ExtractState_1020865()
Dim arr As Variant
Dim r As Range
For Each r In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    arr = Split(r, ",")
    r.Offset(0, 1).Value = Trim(arr(UBound(arr) - 2))
Next r
End Sub

The code assumes your list of addresses is in Column A, and will extract the state info to Column B.

Cheers,

tonyyy

 

[Rick Rothstein]

Hi,


I have an address the following formats in a cell:
Customer Name, 1 Address, 2 Address, City, ST, 00000-0000, US
Other Customer, 1 Address, City, ST, 12345, US


I need a formula to just extract the “ST” state information.

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",200)),600),200))

 

[derricksmith]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",200)),600),200))

Thank you sir! That worked!!!