Compare two files with the corresponding location column

Question

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

sample.xlsx

patrick_digan · Answer

@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!

Community 2020-04-02.yxmd

patrick_digan · Answer

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