Extract State from text field

[lipster26]

Hello,

I'm looking for a solution to extract the 2 characters for States from a free form text field. The 2 characters might be anywhere in the text column. I can also have another table that would contain the states abbreviations to do a lookup. But I'm not sure how to extract the 2 characters from Text column. Thanks for any help.

Text	Desired Result
2021 NY EXT PMT	NY
2017 CEG PA REFUND	PA
LA FRANCHISE TAX PMT RECODING TO ACCOUNT 21156300	LA
MS FRANCHISE TAX PMT RECODING TO ACCOUNT 21156300	MS
TN FRANCHISE TAX PMT RECODING TO ACCOUNT 21156300	TN
CPG-LA2019-20 FRANCHISE TAX PMT RECODING	LA
CPG-MS 2020 FRANCHISE TAX PMT RECODING	MS
CPG-NC 2020 FRANCHISE TAX PMT RECODING	NC
TN FRANCHISE TAX PMT RECODING TO 21156300	TN
2017 PA INCOME TAX NOTICE PAYMENT	PA
2018 TN REFUND	TN

[Vijay_A_Verma · Accepted solution]

Based on the pattern, I can notice that any two alphabets word which appears first is a state in your strings. You can use following formula in a custom column without needing a lookup table

= List.First(List.Select(Text.SplitAny(Text.Trim(Text.Remove([Text],{"0".."9"})),"  -"),(x)=>Text.Length(x)=2))

 But if any two alphabets word appear first which is not a state name, then you would require a lookup table. This approach is suggested by @artemus. 

[artemus]

Add a custom column that splits the Text into words (add additional word bounderies as needed).

Text.SplitAny([Text], " .,;")

Click the new column and choose "Expand to New Rows"

Perform an inner join with your state table in order to filter it.