Formula To Create Categories

Herbiec09

Active Member
Joined
Aug 29, 2006
Messages
250
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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"))
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top