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.

 

[Versejus]

Thak you, but what does it do when e.g. LIKEPUMA is a realy different brand and needs to be maked LIKEPUMA instread of PUMA SE?

 

[István Hirsch]

Thak you, but what does it do when e.g. LIKEPUMA is a realy different brand and needs to be maked LIKEPUMA instread of PUMA SE?

Ref. my post #4, if you are searching for " PUMA " the formula won't find " LIKEPUMA ".

 

[Roampie]

Hi All


I don't know if my problem is related to Versejus or is there a way around this.


Anyway this is my problem


column A column B


R 234,34 R 234,34
-234,34 -R 234,34
567,23 R 567,23


Even if I have formatted column A to Currency, still some figures shows up as -234,34 instead of -R 234,34.


Is there a way that I can fix this ?

Note that column B is empty so there is only data in column A

 

[Roampie]

Problem solved, I have copied over all of the data to a new sheet and just flash filled the column next to it.All of the items is correct with double checked.