Thread: Convert state abbreviations in a column into regions in another column

Excel 2010

Thank you in advance, maybe this action is not that difficult for savvy users.

I have a column containing the regular state abbreviations in a CSV document. I would like to create a formula to convert individual cells containing these abbreviations into grouped geographical regions for sales territories. Crudely thinking, run the formula on Col A and populate the result in Col B.

COL A COL B
CA PACIFIC
OR PACIFIC
WA PACIFIC
FL SOUTHEAST
AL SOUTHEAST
NY NORTHEAST

You would use VLOOKUP for this. Set up a table somewhere (eg in columns X and Y) which contains all those abbreviations in the first column and the corresponding region in the second column. Then in B1 you could have this formula:

=VLOOKUP(A1,X:Y,2,0)

then just copy down to cover the amount of data that you have in column A.

Hope this helps.

Pete

Awesome! I know exactly how to do this as you suggested.
This is going to save me a bunch of time on this task, and for similar purposes.

Thank you very much.

Originally Posted by Pete_UK

You would use VLOOKUP for this. Set up a table somewhere (eg in columns X and Y) which contains all those abbreviations in the first column and the corresponding region in the second column. Then in B1 you could have this formula:

=VLOOKUP(A1,X:Y,2,0)

then just copy down to cover the amount of data that you have in column A.

Hope this helps.

Pete

You're welcome, Steve - thanks for feeding back.

Pete