Seprate set of numbers in to different Category

**metricspace** · 07-10-2009, 09:10 AM

I've begginers knowledge in excel, Please assist me with the problem am facing, It's something like that.

I have different sets of number from six digits to a max of 15 digits

eg.
012345
001005869
Z000000A
K1234567
CF0000009
K880000000
M000000123
Y001234505
AL56231
5649852A

What I want is to get the proper category for this number depending upon there digit for eg.

number starting with Z and of 8 digits belongs to group A
number staring with Y ending with 5 and 10 digits belong to group B
number ending with A and which does not starts with any alphabet has 8 digits belongs to group C.

similary ...

A user need to just key in the number, press tab (or any other button) and group for that number
must be displayed (depending upon the compostion of that numbers).

What I can think of is to first seprate each digits from the given number, verify it and then filter them through the groups to get the final group.

However I don't have any idea about how to go about doing this

or are there some more better ways for getting this done, Please assist

**shg** · 07-10-2009, 09:24 AM

Like this?

Please Login or Register  to view this content.

The formula in B1 and down is

=CHOOSE( (LEFT(A1)="Z") * (LEN(A1)=8) + 2*(LEFT(A1)="Y")* ( (LEN(A1)=5) + (LEN(A1)=10) ) + 3 * (RIGHT(A1)="A") * (LEFT(A1)>="0") * (LEFT(A1)<="9"), "A", "B", "C")

**metricspace** · 07-10-2009, 10:56 AM

Thanks a bunch for the reply,

to be more precise
number starting with CL, CF, K88, and length 10 digits fall in to one group
number staring with Z and ending with A, B, C, D, ... J and of 10 digits fall in other group
number starting with 00 and of 9 digit and staring with 00 and 10 digits with a suffix of W, M, P fall in another group.

Sorry, It might seems bit confusing and complex, but please if suggest me a solution to make this complex task simple , this would be of great great help.

Thank you once gain for the help

metric

**shg** · 07-10-2009, 11:15 AM

So -- the same technique will do that. Can you modify it as necessary?

**metricspace** · 07-10-2009, 01:28 PM

Originally Posted by shg

So -- the same technique will do that. Can you modify it as necessary?

Thanks for a quick response, I'll try what you have suggested, hope I can get required result.

Regards,
metric

**metricspace** · 07-10-2009, 01:51 PM

I tried what you suggested,
=CHOOSE((LEFT(A1)="Z")*(LEN(A1)=8)*(RIGHT(A1)="A")+(RIGHT(A1)="B")+2*(LEFT(A1)="Y")*((LEN(A1)=5)+(LEN(A1)=10))+3*(RIGHT(A1)="A")*(LEFT(A1)>="0")*(LEFT(A1)<="9"),"A","B","C")

I added 2 more conditions in the first part for verifying a group, i.e if its end A or B. The result was correct.

However for verying a group, the set of number have pass thru may be more than 10-12 test, for the first part which I modified have test for ending with A, and B, it goes till J.

And there are 15 such group with there own sets of criteria, will be difficult to add 10 x 15 =150 conditions and then filter the result.

Please correct me if am wrong, awaiting your response.

Thanks,
metric