Function to search a text string for any of several smaller strings

[Gingertrees]

Summary: Inherited some data - now trying to categorize products based on a description in a text field.

Details: Identifying info could be across several fields, so I concatenated the data into a single messy text field. Easiest analogy would be to categorize cars as being made by GM or Ford or Other, based on manufacturer, brand, and model - any or none of which may be present.

My text fields look like this:
CHEVROLET MALIBU
GM CHEVROLET TRUCK
GM TRUCK
FORD FUSION
TOYOTA CAMRY

I was thinking if I put together a function using INSTR with a case function, or maybe an array, I could do this, but I'm unclear where to start.

I have a list of makes and models, and if a make or model is found in my text field, that will produce the manufacturer. So to look at my sample text fields again,
CHEVROLET MALIBU..........GM
GM CHEVROLET AVEO.......GM
GM TRUCK......................GM
FORD FUSION..................Ford Motor Company
TOYOTA CAMRY...............Other

Can anyone help me? Or at least point me in the right direction?

 

[JLeno]

You could add a column with a SEARCH function:

=IF(ISERROR(SEARCH("GM",A1)),IF(ISERROR(SEARCH("FORD",A1)),"Other","Ford"),"GM")

 

[Jonmo1]

Here's a syntax that you can use


=LOOKUP(2^15,SEARCH(F1:F5,A1),G1:G5)

A1 of coarse is the long string being searched.
F1:F5 is a list of substrings you want to find in A1
G1:G5 is the corresponding results for the values in F1:F5


Note, there cannot be any blanks in F1:F5


Hope that helps.

 

[JoeMo]

Extracting the manufacturer's name, if present, is doable, but unless you have some reference elsewhere (like a lookup table), how would Excel know from "Chevrolet Malibu" alone to return "GM"?

 

[Gingertrees]

Here's a syntax that you can use


=LOOKUP(2^15,SEARCH(F1:F5,A1),G1:G5)

A1 of coarse is the long string being searched.
F1:F5 is a list of substrings you want to find in A1
G1:G5 is the corresponding results for the values in F1:F5


Note, there cannot be any blanks in F1:F5


Hope that helps.

I really don't understand how that works, but it does work! I modified it slightly: I made my substring list into a named range "keywords", and corr. manufacturers into another range "manufs". To handle the others, I added an iserror clause. So my formula became

=IFERROR(LOOKUP(2^15,SEARCH(keywords,B2),manufs),"Other")

Thank you so much

 

[Jonmo1]

That formula was actually the winner of a past challenge of the month.
http://www.mrexcel.com/pc18.shtml
See post #5 by Barry Houdini from the thread with all the responses to the challenge.
http://www.mrexcel.com/forum/lounge...cement***-june-july-2008-challenge-month.html

It's a long read, but there was lots of discussion throughout that thread about how it works.