Excel 2007: Extract Text String based on a Defined List of Criteria

[justr8hr]

Hello guys,
Please help this newbie.
I want to search the vendor/Text String (from bank statement)on the list below.
[TABLE="width: 640"]
<tbody>[TR]
[TD]KEEP THE CHANGE TRANSFER TO ACCT
[/TD]
[/TR]
[TR]
[TD]POS #209700 AMERICAN RANCH 12-28 CUSTOMER 1311 PURCHASE ARTESIA CA
[/TD]
[/TR]
[TR]
[TD]STARBUCKS CORP 12/29 PURCHASE 2 SANTA MONICA, CA
[/TD]
[/TR]
[TR]
[TD]ID:BX0364457 IDS PROPERTY CAS DES: PREM
[/TD]
[/TR]
[TR]
[TD]POS 4457311483 MACY'S EAST 483 12/29 CARD PURCHASE CERRITOS, CA
[/TD]
[/TR]
[TR]
[TD]TARGET -30 CUSTOMER PURCHASE #007924 Cerritos CA
[/TD]
[/TR]
[TR]
[TD]CARLTON HAIR INTERNATIO 12/28 CARD PURCHASE
[/TD]
[/TR]
[TR]
[TD]KEEP THE CHANGE TRANSFER TO ACCT
[/TD]
[/TR]
[TR]
[TD]POS CSMC CAFETERIA4007 12/27 PURCHASE
[/TD]
[/TR]
</tbody>[/TABLE]
Initially I am doing this manually. I have decided to document my input so I don't have to input them one by one, only if I just know to use them as reference to automatically extract the text.
Here is My criteria list (the actual one is more comprehensive)
[TABLE="width: 665"]
<tbody>[TR]
[TD]VENDOR
[/TD]
[TD]KIND/CATEGORY
[/TD]
[/TR]
[TR]
[TD]American Ranch
[/TD]
[TD]Groceries
[/TD]
[/TR]
[TR]
[TD]Anthem Blue
[/TD]
[TD]Health Insurance
[/TD]
[/TR]
[TR]
[TD]Autozone
[/TD]
[TD]Car Repairs
[/TD]
[/TR]
[TR]
[TD]Carlton Hair
[/TD]
[TD]Personal Care
[/TD]
[/TR]
[TR]
[TD]Cheesecake Factory
[/TD]
[TD]Meals
[/TD]
[/TR]
[TR]
[TD]Chevron
[/TD]
[TD]Fuel
[/TD]
[/TR]
[TR]
[TD]CSMC CAFETERIA
[/TD]
[TD]Restaurants/Dining
[/TD]
[/TR]
</tbody>[/TABLE]

Once I am able to extract the text based on the criteria, I can then put the right category.
VBA or Excel solutions will be greatly appreciated
Thanks a lot!

 

[justr8hr]

PROBLEM SOLVED!!!! Kudos, hiker95. Your VBA eventually worked. I experimented a bit and found that in order for the code not to return an error is that the target criteria string must have the same case and structure as the extracted text. Wohoooo!!!

 

[hiker95]

justr8hr,

Thanks for the feedback.

You are very welcome. Glad I could help.