Formula to check 10 alphanumerics

Hi everyone

I need help for formula to check the validity of an alphanumeric value in cell. The values are in following shape :

AAAAA0000A

ie first 5 should be alphabets (A to Z), next 4 should be numeric (0 to 9) and last one should be alphabet. And all the six alphabets (5 + 1) should be CAPITAL Letters.

I need formula to check the value of cell whether it fulfills the above mentioned criterion or not.

Regards.

If the cell is A1 which contains your alphanumeric value then in the cell B1 enter the formula =upper(A1)
that will make all the cell value in A1 to CAPITAL letter.

Thanks kishore, but my basic concern is to check validity of structure of 10 ie first five are alphabets, next 4 are numerics & last one is alphabet. Its structure of PAN in India and I need to check from the list whether all are in correct format or not.

Try this:

Formula:

Please Login or Register  to view this content.

Thanks zbor, formula is checking perfectly. Just one question, can't understand the cell range given from A1 to A5 as whats the purpose of this range

It's part of MID function so I must start from there.

MID(text,start_num,num_chars) must take from text LEFT(A4,5): 1st, 2nd, 3rd, 4th and 5th character (num_chars is 1 because only need one character).
So it should say something like this:

MID(text,{1,2,3,4,5}, 1)

and this array I've create by ROW(A1:A5) return this array.

So from ABDCR123... will return A B D C and R.
Then it will take CODE of that characters and check are they between 65 and 90 (A to Z).

Originally Posted by zbor

Try this:

Formula:

Please Login or Register  to view this content.

Your formula fail the following:

ABCDE-354F
ABCDE1E34F

As I understood it need to check code, not to return upper.
So your will fail on AbCDE1234F because it should be FALSE.

Nice spoted Marcol, mine formula didn't check for 10 chars.
I've edited my post #8.

Thanks zbor, Marcol and icestationzbra. Will try to learn more about MOREFUNC.