Forum Discussion

rdstone230's avatar
rdstone230
Regular Visitor
2 years ago

Extracting two letter State abbreviation from Address Field

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 ...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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)