Extract names from string based on value list
 

I have a list of productnames and a list of brandnames.

I want to supply the productnames with an attribute "Brand".

I am currently manually filtering the list of productnames based on
"Contains... Brand" and adding the Brand to the product using copy paste,
but this takes me too long and I can only match records one brand at a time.

Can I make Excell search through the list of productnames and whenever it
comes across a brandname within the string of a productname it will add it to
the column next to it?

Extract names from string based on value list
 

One way
Assume productnames running in A2 down
Assume D2:D10 contains all the brands text

In B2, normal ENTER:
=INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH ($D$2:$D$10,A2)),),0))
Copy down to return required results

If you need an error trap to return neat looking blanks for unmatched cases
(if any), use this in B2, normal ENTER, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$ 10,A2)),),0)),"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX (ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)))

Modify to suit. Voila? hit the YES below
--
Max
Singapore
xde
---
"Pluggie" wrote:
I have a list of productnames and a list of brandnames.

I want to supply the productnames with an attribute "Brand".

I am currently manually filtering the list of productnames based on
"Contains... Brand" and adding the Brand to the product using copy paste,
but this takes me too long and I can only match records one brand at a time.

Can I make Excell search through the list of productnames and whenever it
comes across a brandname within the string of a productname it will add it to
the column next to it?

Extract names from string based on value list
 

For the error trap, try this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($D$2:$D$10,M ATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),), 0))))

Only works with text.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
One way
Assume productnames running in A2 down
Assume D2:D10 contains all the brands text

In B2, normal ENTER:
=INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH ($D$2:$D$10,A2)),),0))
Copy down to return required results

If you need an error trap to return neat looking blanks for unmatched
cases
(if any), use this in B2, normal ENTER, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$ 10,A2)),),0)),"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX (ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)))

Modify to suit. Voila? hit the YES below
--
Max
Singapore
xde
---
"Pluggie" wrote:
I have a list of productnames and a list of brandnames.

I want to supply the productnames with an attribute "Brand".

I am currently manually filtering the list of productnames based on
"Contains... Brand" and adding the Brand to the product using copy paste,
but this takes me too long and I can only match records one brand at a
time.

Can I make Excell search through the list of productnames and whenever it
comes across a brandname within the string of a productname it will add
it to
the column next to it?