search for text string in Excel

[swtrader]

I need to boil down text strings and then complete a lookup that will result in:

Given:

Lookup Table:

SearchTermFound ValueToUse
9001 Jefferson Hardware
W/D Cash Withdrawal
PIP's Fuel

and about 100 other search terms

I need to search each row/cell (about 50 rows at any one time) in a single column of text to see if it contains the search term (looping through all 100+ search terms for each row). (Each row will have only 1 of the search terms, if 1 at all.)

Once the search term is found, it needs to be placed into an adjacent column so the VLookup can generate the proper ValueToUse (in the next adjacent column).

I haven't been able to find any VBA code that will extract the text "SearchTermFound".

thanks for all your help.

an amarillo amateur

 

[xlbo]

Why would you need to extract the search term?

For each iteration of the loop, you know what is being searched FOR so just place that in the adjacent cell.

I would use the FIND method for this, looping through each search term and using FIND / FIND NEXT to find all the entries that match (use the Lookat:= xlpart to make sure you can find text within test)

there is a very good example of how to use FIND/FINDNEXT in help - you should be able to use that as a start for 10. Post back if you have any issues building your code from what I have explained

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

I gather you want to search a range for any member of a set of search terms all at once. That sounds like a complicated form of regexp that I don't think you're going to find already built. You will have to write your own function with something like select case perhaps.

On the other hand, it seems like a poorly defined problem. It might be simpler to implement if you could state more clearly what your objective is.

_________________
Bob Rashkin

 

[AnotherHiggins]

I'm probably being dense, but I don't understand what you are asking. From what you've posted, it seems like this[/i] would be a fine place to use a VLookup.

The only clue I see that might keep that from working is your thread title, "search for text string in Excel", which makes me think that maybe you are looking form partial strings instead of the whole cell. If that is the case, you can use a VLookup like this:
=VLOOKUP([red]"*"&[/red]C1[red]&"*"[/red],A1:B10,2,0)

The asterisks act as wild cards.

Having written this, I now see that there are already some other replies. It seems that I am not alone in finding your post unclear. Please provide more details.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - I beleive that the OP has a table of search terms and the desired value that needs to be placed in the same row.

The search terms may be embedded in more text so cannot use a straight vlookup to access them. Using the FIND method, the "Search Term" can be found and placed in the appropriate row. The OP can then use a vlookup on their "Search Term / Value to Use" table to return the "Value to Use" also onto the appropriate row

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[swtrader]

Sorry for the confusion. Was trying to be brief.

Here's the problem.

Each week, we receive an Excel spreadsheet that has about 50 rows of info:

Date, Description, Amount

The Description cell contains text -- anywhere from a word or 2 to 30 or 40 words. The data is not uniform since the entries come from many different sources. An entry might say: bought gas at PIP's; or filled up at PIP. IF the text string PIP is in the cell somewhere, then we need to be able to show "Fuel" in column D. So, we need to search each row in the Description column to see if it contains one of the search terms. If it does (and it will contain only 1 of the 100 or so search terms), then the corresponding result needs to be in column D.

I thought an extraction of the found text would form the basis for a vlookup and that this might be the easier way to go.

I hope this is clearer. It's a simple problem -- but I'm I'm a simple guy.

thanks for your patience and help.

 

[xlbo]

See my 1st post for how you can do this

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

I'm thinking you might build a collection of the search items as keys and the values as items. Then you could search each string for the key and pull out the value when you get it.

_________________
Bob Rashkin