Big Project - look ups and cross ref, grouping, HELP

[Bravo2003]

Here's one that i think may be un solvable!!

I have 3500+ products i need to group in to categories by using info within the description

Examples of the descriptions are:
10x10_5.5_xth_NN_Blue_PPP
10x10_7.5_xth_NN_Blue_PPR
100x250_5.5_sth_NN_Red_PNCB
75x250_1.5_xthBgd_Misc_PPP

There are roughly 130 group categories that I need to label them in to

So basically, Description is in Column C
In column AO I need to give each line a group code

• which will be based on the size details e.g. 10x10 the colour ref e.g Red and the code xth/sth part of the description
  • 45 size options
  • 4 colour options
  • 4 code options (xth/sth)

So from the 4 examples above I would give these group codes:
10x10_xth_Blue (or a code that this description would fall under like 10xB or 123, etc..)
10x10_xth_Blue
100x250_sth_Red
75x250_xthBgd_Misc
(you see the first two are the same group)

Now the question is… How do I do this???

 

[gaz_chops]

You could use text to columns with "_" as the separator, then concatenate the fields you want.

sorry it won't work!!

 

[gaz_chops]

This works if there are 5 occurrences of"_", returns #VALUE! if more or less!
You could tweak the code for those.

=LEFT(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),1)))&MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),2)))&MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),4))+1,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),4))-1)

 

[Bravo2003]

This works if there are 5 occurrences of"_", returns #VALUE! if more or less!
You could tweak the code for those.

=LEFT(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),1)))&MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),2)))&MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),4))+1,FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),4))-1)

Hmmm.. this works to a point. Unfortunately I don't know these formulas, so don't understand how its working to tweak it?

What it is giving me is (examples

50_5.5_NN
100_5.5_NN

This is almost what I need, but I would ideally need

10x10_xth_Blue

I'll try tweaking if I can (will try do some research on the formulas), but any one who can help appreciated..

Cheers

 

[gaz_chops]

When i test it, it returns what you need 10x10_xth_Blue
what cell is your data in?

 

[Bravo2003]

When i test it, it returns what you need 10x10_xth_Blue
what cell is your data in?

C18 is original text string
AO18 is formula/result

Ah... I spot an issue the 10x10 is actually 10_10 all x's are _ !
sorry only just realised the x has changed in new reports.

 

[gaz_chops]

Will this always be the case?

 

[Bravo2003]

Will this always be the case?

Yes unfortunately, the description will always now be 10_10_etc...

example: 10_10_5.5_xth_NN_Blue_PPP

 

[Bravo2003]

Will this always be the case?

Yes unfortunately, the description will always now be 10_10_etc...

example: 10_10_5.5_xth_NN_Blue_PPP

 

[gaz_chops]

Try
=SUBSTITUTE(LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),2)))&MID(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),3)))&MID(B3,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),5))+1,FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(B3,"_",CHAR(1),5))-1),"_","x",1)