Wildcard Lookup

**353663** · 10-14-2009, 06:12 AM

I’m trying to write a formula that will search for certain words contained in the cells of Column A and then return those words in column B. The contents of each cell will contain more information than the key word being searched for.

The aim will be to import transaction descriptions into Column A and then to search the descriptions for key words which will be used as transaction categories. So for example, Column A may contain…

A
1 1234 petrol
2 4565 joe blogs div
3 Interest Charge

I want to search A1, A2, A3 etc for the words “petrol”, “div” and “Charge” and then return “petrol”, “div” or “charge” into the corresponding row in column B. So that B1 shows “Petrol”, B2 shows “Div”, B3 shows “Charge”. Any help would be much appreciated. I don’t think you can use wild cards with nested IFs so I’m a bit stuck.

**DonkeyOte** · 10-14-2009, 06:17 AM

You can use a variety of IFs

Please Login or Register  to view this content.

Change SEARCH to FIND if you wish for your tests to be case sensitive.

**353663** · 10-14-2009, 06:20 AM

Wow. Thanks very much for the quick reply. This works perfectly!

**DonkeyOte** · 10-14-2009, 06:23 AM

To be safe you should probably revert to

Please Login or Register  to view this content.

eg a string containing Surcharge would not return Charge using the above whereas it would using the previous approach.

**353663** · 10-14-2009, 08:51 AM

Am I going to run into the 7 IF limit with this formular? Is there any way I could have more than 7 categories? Thanks

**martindwilson** · 10-14-2009, 09:44 AM

you could create a table and drag down see attached

**DonkeyOte** · 10-14-2009, 09:54 AM

If you were to create a listing of the keywords, lets say they exist in C1:C10 with strings possibly containing keywords in A1 (and below), then

Please Login or Register  to view this content.

- the above looks for exact matches - ie dog <> dogs ... tread <> read etc...

- should multiple keywords be found in A1 the above will return the last, if you prefer the first the above can be reverted to an INDEX based approach, if indifferent the above is probably the slightly simpler of the two.