Extracting State Abbreviation from a String

[ESACAWIP]

Hello! Having some trouble finding out a way to extract any 2 letter state abbreviations from a string. Most of what I've googled returns pull state values from a address, but in my data, its more like random sentences and words.
-if multiple states found, they will need to be separated by a comma
-the state will always be capitalized in the string (eg. FL, TX, MO)
-in some tricky instances like "(CALIFORNIA and TEXAS and CO)", only the state value "CO" will be pulled since California and Texas are not in the abbreviated form

Much apprevaited!

today is the day in CA where the sun sets	CA
DE is quite far from FL	DE, FL
NY	NY
(CALIFORNIA and TEXAS and CO)	CO

 

[lrobbo314]

Edit, not working

 

[ESACAWIP]

This should do it.

EXCEL
	A	B	C
1	today is the day in CA where the sun sets	CA	CA
2	DE is quite far from FL	DE, FL	DE, FL
3	NY	NY	NY
4	(CALIFORNIA and TEXAS and CO)	CO	CO
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=STATE(B1)

Function STATE(s As String)
Dim AL As Object:   Set AL = CreateObject("System.Collections.ArrayList")
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[A-Z]{2}"
    Set matches = .Execute(s)
    For Each m In matches
        AL.Add m.Value
    Next m
End With
STATE = Join(AL.toarray, ", ")
End Function

Thank you for the fast response lrobbo314

Its working for all cells except C4 "(CALIFORNIA and TEXAS and CO)" im getting a strange output. Its returning "CA, LI, FO, RN, IS, TE, XA, CO"

 

[Akuini]

@ESACAWIP, are you willing to use VBA?

 

[Akuini]

Try this UDF:

Function to_State(a As String) As String
Dim tx As String
Dim Matches As Object, m
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b[A-Z]{2}\b"
        If .test(a) Then
            Set Matches = .Execute(a)
            For Each m In Matches
              tx = tx & ", " & m
            Next
        End If
End With
to_State = Mid(tx, 3)
End Function

 

[FormR]

Hi, here's another option that seems to work for the examples presented.

Book1
	A	B
1	today is the day in CA where the sun sets	CA
2	DE is quite far from FL	DE, FL
3	NY	NY
4	(CALIFORNIA and TEXAS and CO)	CO
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LET(TS,TEXTSPLIT(A1,{" ","(",")"}),TEXTJOIN(", ",1,FILTER(TS,(LEN(TS)=2)*EXACT(TS,UPPER(TS)))))