# Thread: Find if cell contains a state abbreviation

1. 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

2. 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.

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

4. [quote name='cchambers@work' post='779330' date='11-Jun-2009 10:29']I really need to get better at arrays![/quote]

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

Steve

5. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•