Remove Brand and Category from Product Description

Hi,

I have a product description in column B and would like to remove any reference to the size or packing (end of description), and Brand (front of description) and Category (usually just before the packing).


CocoNara Coconut Chocolate 24*60Pcs (Brand: CocoNara)
Coco Nara Coconut Chocolate 18/96Pcs (Brand: Coco Nara)
Coco Nara Aya Coconut Chocolate 18/96Pcs (Brand: Coco Nara Aya)

I have figured out how to remove the size/packing by FINDing spaces in the description (Columns D through I) and category (Chocolate) separate data column.

Column N: Brand - 1st row: CocoNara 2nd row: Coco Nara 3rd row: Coco Nara Aya
Formula: =IF(B2="Chocolate",IF(ISNUMBER(FIND("Coconut",A2)),LEFT(A2,FIND("Coconut",A2)-2),"N"))

Column O: Category - all 3 rows: Chocolate
No Formula:

Column P: Removes the size/packing - Results: 1st row: Coconut Chocolate 2nd row: Nara Coconut Chocolate 3rd row: Nara Aya Coconut Chocolate
Formula: =IF(D2=5,MID(A2,E2+1,I2-E2-1),IF(D2=4,MID(A2,E2+1,H2-E2-1),IF(D2=3,MID(A2,E2+1,G2-E2-1),IF(D2=2,MID(A2,E2+1,F2-E2-1),MID(A2,E2+1,LEN(A2)-E2)))))
Problem starts here, but why?

Column Q: Finds Sub Category - Results: 1st row Coconut 2nd row: Nara Coconut 3rd row: Nara Aya Coconut
Formula: =IF(O2="Chocolate",TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(P2,N2,"")),"Chocolate","")),P2)
Above formula works fine if the Brand is a single word, but if the Brand has 2 or more words, the result in Column Q does not work.
I tried substituting the "N2" in the formula with the actual brand name and still did not work. I tried to not use TRIM() and still did not work. I have run out of ideas.

Any help is appreciated, I have attached the worksheet.

Warmest regards
RJ

Hi Nigel,

So simple and efficient formula. Thank you very much. It seems I was trying to eat the cake going behind my head, LOL

Only adjustment I made was to add TRIM to the formula to eliminated a space in the first position of the cell.

Again thank you for all your help.

RJ