Formula To Create Categories

[Herbiec09]

Hi All.

I have a list of product codes , which I would like to turn into product categories in the next column (sample product codes attached).

The rules are as follows:

If the code contains the letter L and either an L or an E, then the category is LL.

If the code contains the letter L and either an O or an R, then the category is LO.

Otherwise "No Category"

Can someone assist with a formula suggestion or recommend a solution. I thought this was quite tough as Excel has to look for certain text within a string.


Product codes are anywhere between 3 characters to 5 characters long

A sample list of the codes is here: https://www.dropbox.com/s/8u9isfkymeyz3on/Product Codes.xlsx?dl=0


Many thanks

HerbieC09

The Sample List

L1E
L1EA
L1EAD
L1EF
L1EFA
L1EKV
L1EV
L1EVA
L1L
L1LA
L1LF
L1LFA
L1LG
L1LK
L1LQ
L1LV
L1LVA
L1MD
L1O
L1OA
L1OF
L1OFA
L1OK
L1OV
L1OVA
L1R
L1RA
L1RF
L1RFA
L1RV
L1S
L1SA
L1SGA
L2E
L2EA
L2EAD
L2EAQ
L2ED
L2EF
L2EFA
L2EG
L2EGA
L2EGD
L2EGQ
L2EK
L2EKA
L2EKD
L2EKG
L2EKQ
L2EKV
L2EQ
L2EV
L2EVA
L2EVD
L2EVQ
L2L
L2LA

 

[mikerickson]

Perhaps
=IF(OR(ISNUMBER(MATCH("*L*O*",A15,0)),ISNUMBER(MATCH("*O*L*",A15,0)),ISNUMBER(MATCH("*L*R*",A15,0)),ISNUMBER(MATCH("*R*L*",A15,0))), "LL", IF(OR(ISNUMBER(MATCH("*L*O*",A1,0)),ISNUMBER(MATCH("*O*L*",A1,0)),ISNUMBER(MATCH("*L*R*",A1,0)),ISNUMBER(MATCH("*R*L*",A1,0))), "LO","none"))

 

[Aladin Akyurek]

In B2 enter and copy down:

=IFERROR(IF(LEFT(A19)="L",LOOKUP(9.99999999999999E+307,SEARCH({"L","E","O","R"},REPLACE(A19,1,1,"")),{"LL","LL","LO","LO"})),"No Category")

 

[Herbiec09]

Thank you

 

[Aladin Akyurek]

Thank you

Thanks for the update.