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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
    rdstone230
    Regular Visitor
    2 years ago

    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's avatar
    rdstone230
    Regular Visitor
    2 years ago

    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.