Diagnosis of a word

[hsandeep]

My column contains a word which MUST contain AAAAANNNNA (A-Z) i.e. 10 digits 1st 5 Alphabets, next 4 numerical (0-9) & 10th Alphabet (A-Z). If it doesn't next column should give the mistake :
i.e.

1. less than 10 digits
2. 3rd value not an alphabet
3. OKAY

 

[hsandeep]

Please SEPARATE INDIVIDUALLY all the 10 (or whatever nos. available) in 10 columns subsequently. This may also help me.
Ex: AATYP0623K as A A T Y P 0 6 2 3 K

 

[J.Ty.]

I have made a solution using a "what-if data table". It is manifested by the formulas {=TABLE(,D1)}. You cannot enter them manually, follow instructions below.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	ABCDS1234E	OK		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
3	KFDBXZ9462Q	not 10 symbols		not 10 symbols	1st symbol not in "A-Z"	2nd symbol not in "A-Z"	3rd symbol not in "A-Z"	4th symbol not in "A-Z"	5th symbol not in "A-Z"	6th symbol not in "0-9"	7th symbol not in "0-9"	8th symbol not in "0-9"	9th symbol not in "0-9"	10th symbol not in "A-Z"
4	JKFNDF2345	6th symbol not in "0-9"
5	SSSS09876	not 10 symbols
6	aaaaa1234a	OK
7
Sheet4

Cell Formulas
Range	Formula
E1	=MID($D1,COLUMN(A2),1)
E2	=NOT(ISNA(MATCH(E1,letters,0)))
F1	=MID($D1,COLUMN(B1),1)
F2	=NOT(ISNA(MATCH(F1,letters,0)))
G1	=MID($D1,COLUMN(C1),1)
G2	=NOT(ISNA(MATCH(G1,letters,0)))
H1	=MID($D1,COLUMN(D1),1)
H2	=NOT(ISNA(MATCH(H1,letters,0)))
I1	=MID($D1,COLUMN(E1),1)
I2	=NOT(ISNA(MATCH(I1,letters,0)))
J1	=MID($D1,COLUMN(F1),1)
J2	=NOT(ISNA(MATCH(J1,digits,0)))
K1	=MID($D1,COLUMN(G1),1)
K2	=NOT(ISNA(MATCH(K1,digits,0)))
L1	=MID($D1,COLUMN(H1),1)
L2	=NOT(ISNA(MATCH(L1,digits,0)))
M1	=MID($D1,COLUMN(I1),1)
M2	=NOT(ISNA(MATCH(M1,digits,0)))
N1	=MID($D1,COLUMN(J1),1)
N2	=NOT(ISNA(MATCH(N1,letters,0)))
N6	=IF(ISBLANK(D1),"",IF(AND(D2:N2),"OK",INDEX(D3:N3,MATCH(FALSE(),D2:N2,0))))
D2	=LEN(D1)=10
B1	=N6
A1:B6	{=TABLE(,D1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
digits	={"0","1","2","3","4","5","6","7","8","9"}
letters	={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}

In order to get the desired result :
1) mark the whole area with words (A2:A6 in my case) together with the next column for results together with an extra row above it.
2) use Data->What-if analysis-> Data table
3) give D1 as the "column input cell", leave "row input cell" blank, click OK
4) enter =N6 in the cell B2.

Then Excel will automatically enter each of your words in column A into D1, compute all cells in columns E to N with the final result N6, and then transfer that final result into column B in the appropriate row.

If you wish, you can download my worksheet from this address: http://www.mimuw.edu.pl/~jty/MrExcel/hsandeep.xlsx
The solution does not distinguish capital letters from small ones so far. If it is a problem, let me know.

J.Ty.Redall

 

[hsandeep]

Extremely nice. But only in A4 JKFNDF2345. If I make it JKFND523F5 it should say 9th symbol not in "A-Z"

 

[Rick Rothstein]

How about a UDF (user defined function) that will do what you want...

Function CheckCode(S As String) As Variant
  Dim x As Long
  If Len(S) < 10 Then
    CheckCode = "Code has too few characters!"
  ElseIf Len(S) > 10 Then
    CheckCode = "Code has too many characters!"
  Else
    For x = 1 To Len(S)
      If x < 6 Or x = 10 Then
        If Mid(S, x, 1) Like "[!A-Z]" Then
          CheckCode = CheckCode & ", Character #" & x & " should be a upper case letter"
        End If
      ElseIf Not Mid(S, x, 1) Like "#" Then
        CheckCode = CheckCode & ", Character #" & x & " should be a digit"
      End If
    Next
    If Len(CheckCode) Then
      CheckCode = Mid(CheckCode, 3) & "."
    Else
      CheckCode = "Okay!"
    End If
  End If
End Function

Note: If the text is the correct length, then the above code will identify all the mistakes in it (as a comma delimited list).

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CheckCode just like it was a built-in Excel function. For example,

=CheckCode(A1)

 

[hsandeep]

Can someone help to split all the 10 digits into 10 SEPARATE

 

[pgc01]

Can someone help to split all the 10 digits into 10 SEPARATE

With the code in A1, in B1:

=MID($A1,COLUMNS($B1:B1),1)

Copy across

 

[hsandeep]

Thanks pgc01. Now I'll try to do something so that 1st 5 are FOUND TO BE alphabets (A-Z), 6th to 9th numericals (0-9) & 10 th Alphabets (A-Z). Can someone help me in this?

 

[J.Ty.]

Extremely nice. But only in A4 JKFNDF2345. If I make it JKFND523F5 it should say 9th symbol not in "A-Z"

It tells you always the FIRST reason why the word is wrong. In this case this reason is that the 6th symbol is not in 0-9.

Concerning splitting into single symbols, my code does it using formulas MID...

J.Ty.

 

[J.Ty.]

Thanks pgc01. Now I'll try to do something so that 1st 5 are FOUND TO BE alphabets (A-Z), 6th to 9th numericals (0-9) & 10 th Alphabets (A-Z). Can someone help me in this?

I did it already. Read the code and, if you dislike what-if data tables, the formulas are there anyway, but test only cell D1. Of course, you can rearrange them into a different shape and copy to process all the remaining cells.

J.Ty.