Extracting two letter State abbreviation from Address Field

[rdstone230]

I'm looking to extract the two letter State abbreviation from an address field that has an inconsistent pattern.  I tried filtering as:

=List.Select(Address Field, each Text.Length(_) = 2)

but the results were not consistent.

Address Field

7904 NW Tiffany Springs Pkwy, Kansas City, MO 64152, USA

F6X5+C4 Beebeetown, IA, USA

J27C+29 Modale, IA, USA

W6W4+GX Sentinel Butte, ND, USA

I-94, Terry, MT 59349, USA

US-395, LIND, WA 99341, USA

[dufoq3]

Hi @rdstone230, try this. It also handles Country Code with space cases (just for sure).

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCoJAFIXhVzm4dYTSUZmlGoWVFqiMIC6MxpBkDJ0Q3z7dJG1aXr7/cItCc9mGIuZIm7qu5ITk1TfyMeD6HCeCUyWHakDQqPmILnDo1jYJssTTSlJoeye39YDCF+ImhOpGSRB6qx9NN9BNhqi7V634Ne5wqh9yJEKqRooW/lupuYl3axMajBKkou+X9ylsZlG2cpYYFrMJzuEy4h7Y7Nv/jm9QfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Address Field" = _t]),
    Ad_State = Table.AddColumn(Source, "State", each 
        [ a = Splitter.SplitTextByAnyDelimiter(Text.ToList(" ,"))([Address Field]),
          b = List.Select(a, (x)=> Text.Length(x) = 2 and Text.Length(Text.Select(Text.Upper(x), {"A".."Z"})) = 2),
          c = List.Last(b)
        ][c], type text)
in
    Ad_State

[ronrsnfld]

This should work even if some are missing the Country Code at the end. It looks for the last space or comma separated substring that consists of just two capital letters.

Note: Code edited to ensure two character string are, indeed, Letters.

   stateAbbrev = Table.AddColumn(#"Previous Step","State", (x)=>
        let 
            split = List.Reverse(Text.SplitAny(x[Address Field]," ,")),
            twoCaps = List.Select(split, each Text.Length(_)=2 and Text.Upper(_)=_ and (try Number.From(_))[HasError])
        in 
            twoCaps{0}, type text)

[Greg_Deckler]

@rdstone230 Table.AddColumn(#"Changed Type", "Custom", each let splitColumn1 = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Column1])) in Text.Start(splitColumn1{1}?, 2), type text)

[rdstone230]

@Greg_Deckler I was able to have it work by chaging the formula to:
Table.AddColumn(#"Changed Type", "Custom", each let splitColumn1 = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Column1])) in Text.Start(splitColumn1{1}?, 3), type text)

This solved it!  Thank you very much for your help!

[rdstone230]

@Greg_Deckler  - this is very close, but its not returning me with the 2 letters.  The outcome is only the first letter of the two letter State abbrevaition.