Finding a substring within a string

I have a spreadsheet I received from one of my suppliers that has about 2500 rows. Rather than separating the relevant data for each product, they put it all in a single column of cells marked "description".

Here's a typical example:

HANDLE SPRINGS ZN MATT NICKEL M4 CTC 128MM

What I want to do is analyze the contents and see if it contains a certain sub-string. If it finds it, I'd like to consult a V Look-up table so that it will return the term I want.

The sub-strings I'm looking for are "ZN", "AL", or "ORB". So in the above example, the function would find "ZN", and then go to the V look up table, returning the final value of "Zinc".

Any help greatly appreciated!

Hi there,

Not particularly elegant, but if it is only the three values you're looking for you could use this formula:

=IF(ISNUMBER(SEARCH("ZN",A1)),"Zinc",IF(ISNUMBER(SEARCH("AL",A1)),"Aluminium",IF(ISNUMBER(SEARCH("ORB",A1)),"Orbitinium")))

Just change A1 to the cell with the description in and the "Zinc","Aluminium" and "Orbitinium" (everyone's favourite fictional element!) to whatever you need. Drag down!

HTH,

FermentedR

Thanks! That did work, and I hate to look a gift horse in the mouth, but I have to confess that to keep the example brief I didn't say that there could be more than the 3 possibilities I cited. From what I can see there could be as many as 7, perhaps more, so I'd love to keep the formula as simple as possible.

Is there a way to perform the task with a V look-up table? This way once the formula is in and copied, it's easy to change the sub-string I'm looking for and it's result as well.

See attached. I "expanded" (to put it mildly) your solution. As it turned out, the original manager didn't keep his naming conventions as consistent as he could have, so each variation needed a new entry.

Sheet 2 has the V look up table

Thanks for having a look!

Sweet!!!!!

Because the site was having server issues today I didn't even realize you had posted a solution to my problem.

A HUGE Thank you!!!!!

Sweet!!!!!

Because the site was having server issues today I didn't even realize you had posted a solution to my problem.

A HUGE Thank you!!!!!