Compare two files with the corresponding location column

[massahkachlan]

I have a dataset, I would like to first trim out the states abbreviation and have it in a separate column, I tried to use Regex tool but it did not work because the state are not uppercase. 

after that I would like to add a column with corresponding state for each state abbreviation ( i made a separate file that has 50 states and abbreviation.

I attached below sample data. Thank you

[patrick_digan]

@massahkachlan Can you provide a sample of your data and the regex expression that isn't working for you? 

[patrick_digan]

@massahkachlan see attached workflow. I've added a space at the end of your addresses. Then I have a list of states and state abbreviations. I added a space before and after the abbreviation for a lookup. I use the find/replace tool to find the lookup in the address field. Hopefully that points you in the right direction!