Creat a list box of groups of items

[Anonymous]

Here is my product list: AA111, AA112, AA113, BB121, BB122, BB123, CC131, CC132, CC133, DD141, DD142, DD143. You can see they are four different groups of products: AA, BB, CC, DD

I created a chart showing total sales for the four groups by month.

How can I create a Group field which contains AA, BB, CC, DD so I can assemble a list box of Groups? 

[Nicole-Smith]

You can use a calculated expression on your list box:

=Left(Product,2)

See attached for example.

[Anonymous]

Thank you, Nicole. I might didn't set the scenario so well. The actual product is like ABA**, ABA***, ABA****, BE*, BE***, BE****, ACED*, ACED****, ACED**, BAA***, BAA*

So, four groups, ABA, BE, ACED, BAA.

[Nicole-Smith]

If you just need to get rid of the numbers:

=PurgeChar(Product,'1234567890')

[rwunderlich]

How about

=purgechar(Product,'0123456789')

-Rob

[Anonymous]

Thank you...but the both the group names and the item names are mixed with number and letters.

Basically I just need to sort the items into four groups. The 1st group is all the items with "AB1" in their names, the 2nd group is the ones with "BC23", the 3rd is with "VD324", the 4th is "MM392"

Then I want to create a list box of "AB1", "BC23", "VD324", "MM392"

[Anonymous]

Hi, Rob. It's great to see you here! Unfortunately I was not trying to get rid of the letters...

[Nicole-Smith · Accepted solution]

Okay, here's another shot at it:

=pick(wildmatch(Product, 'AB1*', 'BC23*', 'VD324*', 'MM392*'), 'AB1', 'BC23', 'VD324', 'MM392')

Example attached.

[CELAMBARASAN]

Hi,

Is there any logic that you have to identify the Product groupings from the product name/number

Assume that ABA11123 is the product name

you are telling that ABA1 is the product group, Why not there is a possibility of product group called ABA.

To provide you a solution we need some clear explanation on this differentiation of product group from the product name/number

[Anonymous]

Nicole, I tried it but the list box listed all the product numbers not the four groups, "AB1", "BC23", "VD324", "MM392"....