Categorize set of number into different group

[metric]

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

 

[PA HS Teacher]

How many groups are there?

My initial thought would be this:
Assuming you have the Number in Column A,
Make a different column for each Group that evaluates to true or false.

For example, your first condition described above might look like:
In B2: =And(Left(A2,1)="Z",Len(A2)=8)

Your Second Condition might look like:
In C2:=And(Left(A2,1)="Y",Or(IsNumber(--Right(A2,5)),IsNumber(--Right(A2,10)))

 

[metric]

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 soln to solun. to make this complex task simple , this would be of great great help.

Thank you once gain for the help
metric

 

[metric]

How many groups are there?


For example, your first condition described above might look like:
In B2: =And(Left(A2,1)="Z",Len(A2)=8)

There are 20 groups. (each with their own criteria, the cirteria is such that a number can only belong to one group)

The above formula seems to be working, am trying out applying various filters using what you have suggested.

Please assist is there a way such that instead of using "Z" I can give a range of cell reference that have letter from A, B, C ... J. so that it returns TRUE when the number starts with A or B or C or .... J.

Thanks,
metric

 

[PA HS Teacher]

I would use the Code Function.
Code("A") = 65
Code("J") = 74
Code("Z") = 65+25

e.g.
=And(Code(Left(A2,1))>=65,Code(Left(A2,1))<=74)

should return true if the String in A2 starts with any letter from A to J

 

[Legacy 68668]

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 soln to solun. to make this complex task simple , this would be of great great help.

Thank you once gain for the help
metric

Can you show us few sample data for each category ?

 

[metric]

Sample Entries
Group A
CL00000001
K883216548
CF98745632

Group B
Z1015263A
Z2026253B

Group C
5968421A
1234567C
AL123456
UL123456

Group D
0020123
2013564
2568932

Group E
0010012345
0010032653

Regards,
Arif

 

[metric]

I would use the Code Function.
Code("A") = 65
Code("J") = 74
Code("Z") = 65+25

e.g.
=And(Code(Left(A2,1))>=65,Code(Left(A2,1))<=74)

should return true if the String in A2 starts with any letter from A to J

Yes, the above trick can be used when we have group ending with A..J,
However I do have groups for which I need to test if there first 2 digits are any of

CL, CW, CF, K8, UL, CK, ...

In verifying the first 2 digits with OR command will be useful, want to try to reduce the number of OR statement, any hint is welcome.

and yes, thank you once again for the response.

metric

 

[Legacy 68668]

UDF
1) Hit Alt + F11 to open vbe
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again to get back to Excel
Use in cell like
=myGroup(A1)
then fill down

Private regX As Object
 
Function myGroup(ByVal txt As String) As String
Dim a,
If regX Is Nothing Then Set regX = CreateObject("VBScript.RegExp")
With regX
    .Pattern = "^((CL|CF)\d{8}|K88\d{7})$"
    If .test(txt) Then
        myGroup = "A"
        Exit Function
    End If
    .Pattern = "^Z\d{8}[A-J]$"
    If .test(txt) Then
        myGroup = "B"
        Exit Function
    End If
    .Pattern = "^(\d{9}[A-J]|[A-Z]L\d{8})$"
    If .test(txt) Then
        myGroup = "C"
        Exit Function
    End If
    .Pattern = "^\d{7}$"
    If .test(txt) Then
        myGroup = "D"
        Exit Function
    End If
    .Pattern = "^00\d{8}$"
    If .test(txt) Then
        myGroup = "E"
        Exit Function
    End If
End With
End Function

 

[metric]

Thanks for the reply, I'll try this, Lets hope it can solve my query, else =AND().. and =OR , I'll try to handle with =AND() and =OR

Thanks to all of you for a very good reponses

Regards,
metric