Find State Abbreviations in String and Post in Separate Column

[griffinco]

Hello All,

I'm looking to create an additional column in my table that lists the State Abbreviation if any are found in a string for Column A. If not I'd like the cell to say "CW" for country-wide (easy part of the formula.)

Not sure how to go about this. Possibly RegEx matching or VLOOKUP? Or something completely different? Preferably a formula for the cell that automatically does this for all my other files!

Here is one of the files I'm working with and a second one containing the State Abbreviations:

StateAbbExtract.txt
state-abbreviations.xls

Thanks and Cheers!
Jamie

[dilipandey]

Hi griffinco,

In Excel, do you want column B to show the expanding of the abbreviations in column A?
If so where is the master list of the abbreviations?

Regards,
DILIPandey

<click on below 'star' if this helps>

[griffinco]

Hi DILIPandey,

Thanks for the quick response. The attached txt file would be in Column A and remain untouched. Column B would list the state abbreviations (a list of all state abbreviations is in the second attached file) if there are found in Column A (or "CW" if none are found.) This way I can create a table and sort the form names in Column A by state.

Cheers,
Jamie

[dilipandey]

Hi Jamie,

See the attached file where I have created the scenario mentioned by you.. now you need to come up with different situations so that I can accommodate those in next revision. thanks.
state-abbreviations.xls


Regards,
DILIPandey

<click on below 'star' if this helps>

[griffinco]

Thanks for taking a look at this DILIPandey.

Here's an example of what I'm looking for:

In Row 8, Column A is "ILIDCARD". IL is a state abbreviation, so I would like C8 to be "IL" (instead of CW.) Hope that helps clarify what I'm looking to do!

[dilipandey]

Okay..

See the revised attachment.

state-abbreviations.xls
Regards,
DILIPandey

<click on below 'star' if this helps>

[griffinco]

I'm a bit confused...the cells in Column 8 just say "found" instead of listing the state abbreviation that was found. And it doesn't recognize the abbreviations in the middle of strings like in rows 16-22.

[dilipandey]

I know and that is why I mentioned following in my post#4 :-

now you need to come up with different situations so that I can accommodate those in next revision

For Column 8, you can use the below revised formula:-
=IF(ISNUMBER(MATCH(LEFT($A8,2),$B:$B,0)),LEFT($A8,2), "CW" )

But then you need some characters from middle of the string like you mentioned

And it doesn't recognize the abbreviations in the middle of strings like in rows 16-22.

Suggest you to come up with all possible scenarios and highlight them on excel workbook and upload so that I can update formula to handle everything. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[jindon]

This will update the txt file.

Place both files in a same folder and run the code.

Please Login or Register  to view this content.