Find if cell contains a state abbreviation

Topic

New Reply

I need to perform a kind of reverse lookup. I am going to have a huge list of addresses (see sheet 2) where the city/state/zip could show in any of 5 possible address lines. I want to perform a check (see columns F-J in sheet 2) that will give me a Y/N response if each of the corresponding address lines reflect a valid state. The states are listed in sheet 1. I’ve tried different variations of a FIND, SEARCH, and VLOOKUP. Each one of those is where you’re looking for a specific thing in a whole list of data. I looking for the possiblity of a whole list of data within each single cell. Where am I getting stuck on how to do this?

Thanks as always!
Christine

Reply | Quote

Replies

Enter the following array formula (confirm with Ctrl+Shift+Enter) in cell F2 on Sheet2:

=IF(SUM(1*NOT(ISERROR(SEARCH(” “&Sheet1!$B$2:$B$60&” “,A2))))>0,”Y”,””)

Fill right to J2, then (with F2:J2 selected) fill down as far as needed.

Reply | Quote

This rocks…as usual! I really need to get better at arrays! Thanks again, Hans!!!!

Reply | Quote

I really need to get better at arrays!

A couple excellent tutorials are by the MS MVPs Bob Umlas and Chip Pearson

Steve

Reply | Quote

The key element in the formula is placing a space before and after the state name in ” “&Sheet1!$B$2:$B$60&” “, so that the SEARCH function won’t pick up two letter combinations in longer words. Without the spaces, SEARCH would find AL in CALIFORNIA and RI in FLORIDA.
There’s a small chance of “false positives”, for example if an address line contains IN or OR as a word instead of a state abbreviation.

Reply | Quote