Extract text string fromcell based on multiple criteria

[Versejus]

Hi,

I'm looking for a solution for the following issue. I need to extract a specific text string from a cell and return a value that belongs to the found text string.

Let me be more specific. I hava a list with data that contains a band somewhere in the cell like this:

Shoe | BRAND A | size 45
T-Shirt | BRAND B| size XL
Sneekers | China | size 35 | BRAND A
Shoe | size 12 | Brand c
T-Shirt |BRAND AZ| size M
etc.

Note that the brand is not at a fixed place, can be in capitals or not, a brand can be part of another brand, is inbetween | or not and has different number of spaces before or after the brandname.

What I want as an outcome is the following:

colum A colum B

Shoe | SPIKES | BRAND A | size 45 BRAND A
T-Shirt | BRAND B| size XL BRAND B
Sneekers | China | size 35 | BRAND A BRAND A
Shoe | size 12 | Brand c BRAND C
T-Shirt BRAND AZ size M BRAND AZ

Initially I made a translation table on the sheet "Brands" with all options and the value that needs to be returned. Like this:

Colum A Colum B

BRAND A BRAND A
BRANDA BRAND A
BARND A BRAND A
etc.

And used the following formula to return the Brand in the cell right to the cell that has to be examined:

=INDEX(Brands!$B$2:$B$1000;MATCH(TRUE;ISNUMBER(SEARCH(Brands!$A$2:$A$1000;INDIRECT("RC[-1]";0)));0))

Trouble is that BRAND AZ is marked as BRAND A. Therefore I tried to make the search strings unique:


Colum A Colum B

| BRAND A | BRAND A
|BRAND A | BRAND A
|BRAND A| BRAND A
...

etc.

Throuble here is that for all brands you have at least 5 options that are used reguarly and the calculation time skyrockets. (The actual unique brands is close to 12,000 and the number of line items is huge!). Therefore I'm looking for a smarter solution that requires less calculation time like using e.g. a search formula with multiple criteria or any other smart solution.
The brands are mostly in capitals but offten there are also other words in capital that don't refere to a brand, this is why just extracting the capital word out of the text did not work either.

 

[63falcondude]

This works for the sample data that you provided

Excel 2010
	A	B
1	String	Brand
2	Shoe | BRAND A | size 45	BRAND A
3	T-Shirt | BRAND B| size XL	BRAND B
4	Sneekers | China | size 35 | BRAND A	BRAND A
5	Shoe | size 12 | Brand c	BRAND C
6	T-Shirt |BRAND AZ| size M	BRAND AZ
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(UPPER(SUBSTITUTE(MID(A2,SEARCH("Brand",A2),8),"|","")))

 

[Versejus]

Hi,

Thank you for your reply. I'm afraight it is a little more complex than that, but this is due to the way I choose to display the problem. Instead of Brand A etc there is an actual brand, like e.g. Puma, Adidas, Asics etc. . There is no common word.

 

[István Hirsch]

„BRAND A” will be found in „BRAND AZ” but „ BRAND A „ will not be found in „ BRAND AZ „ so use this principle when searching for the brand names.

 

[MrIfOnly]

How about this:

Book1
	A	B
1	String	Brand
2	Shoe | BRAND Puma | size 45	Brand Puma
3	T-Shirt | BRAND adidas| size XL	Brand Adidas
4	Sneekers | China | size 35 | BRAND nike	Brand Nike
5	Shoe | size 12 | Brand charlie	Brand Charlie
6	T-Shirt |BRAND AriZona | size M | other text	Brand Arizona
Sheet1

Cell Formulas
Range	Formula
B2	=PROPER(LEFT(RIGHT(A2,LEN(A2)-SEARCH("BRAND",A2)+1),SEARCH("|",(RIGHT(A2,LEN(A2)-SEARCH("BRAND",A2)+1)&"|"))-1))
B3	=PROPER(LEFT(RIGHT(A3,LEN(A3)-SEARCH("BRAND",A3)+1),SEARCH("|",(RIGHT(A3,LEN(A3)-SEARCH("BRAND",A3)+1)&"|"))-1))
B4	=PROPER(LEFT(RIGHT(A4,LEN(A4)-SEARCH("BRAND",A4)+1),SEARCH("|",(RIGHT(A4,LEN(A4)-SEARCH("BRAND",A4)+1)&"|"))-1))
B5	=PROPER(LEFT(RIGHT(A5,LEN(A5)-SEARCH("BRAND",A5)+1),SEARCH("|",(RIGHT(A5,LEN(A5)-SEARCH("BRAND",A5)+1)&"|"))-1))
B6	=PROPER(LEFT(RIGHT(A6,LEN(A6)-SEARCH("BRAND",A6)+1),SEARCH("|",(RIGHT(A6,LEN(A6)-SEARCH("BRAND",A6)+1)&"|"))-1))

Regards,

CJ

 

[Versejus]

Thank you CJ,

The word brand is not a standard part of the cells. I'll add a few cells of the actual data I need to extract the brands from:

Wall Lamps | lighting |FARO licensing

The LED light | lighting |MOLUX licensing

He shook his head light | stage by the | Brand | Iron + no plastic

625 chassis LED bulb | lighting |K-LITE licensing

The LED light bar | decorative lighting with | unlicensed |

Beam Lamp lighting |WOWLITE | Licensing |

<tbody>
</tbody><colgroup><col></colgroup>

What it needs to return is:
FARO
MOLUX
unbranded
K-LITE
unbranded
WOWLITE

At which unbranded is optional.

 

[MrIfOnly]

I see now. This is a bit more complicated. Do you know in advance all of the brand names that may be used? If so, you could create a named range that contains them, and, using an array formula, maybe we can incorporate that into 63falcondude's or my formula.

Regards,

CJ

 

[Versejus]

I do have a list of brands and the name it needs to reply, e.g WOWLITE is also written as WOW-LIGHT and in the end I wan to run a pivot on the results to calculate the totals of revenue (in a different cell). So I do have a table with the brand and the return value. Problem is that there are brands that are a part of another brand e.g. GE and GENERAL or LODGE.
This a list of 12,000 brands and return vanlues. In order to concur the partial name effect I tried to make every lookup value unique but this results in a sixfold of the brands list. (which makes it 72,000) and I need to run this over approximately 1,000,000 records. See my problem...?

 

[István Hirsch]

Similar problem has been solved like this: a "keyword" was chosen for each brand, say PUMA, and if this keyword appeared in the string to be processed, a standard name was extracted. For example, if the string contained either PUMA ltd. or PUMA Corp. or PUMA(LTD), it was substituted with the standard name, say PUMA SE.

 

[MrIfOnly]

See my problem...?

Yes I do!

I'll be dreaming about this one tonight.