Checking pattern in cells

[balburam]

Hello all

I have a column which has combination of texts and numbers. I would like to check it and see if the cells are in the A9A9A9 pattern (A- letters & 9 - for numbers) and in the length of 6.

How can I check its pattern? Is there a function I could use?

Please help!

Thanks and appreciate your help in advance
Balburam

[Paul]

Hi Balburam,

You could use a function like this to check each digit/character:

=AND(LEN(A1)=6,LEFT(A1,1)>="A",LEFT(A1,1)<="Z",MID(A1,2,1)+0>=0,MID(A1,2,1)+0<=9,MID(A1,3,1)>="A",MID(A1,3,1)<="Z",MID(A1,4,1)+0>=0,MID(A1,4,1)+0<=9,MID(A1,5,1)>="A",MID(A1,5,1)<="Z",MID(A1,6,1)+0>=0,MID(A1,6,1)+0<=9)

If any of the tests fail, the result will be FALSE (or #VALUE error).

[Cutter]

Maybe

=AND(LEN(A2)=6,COUNT(MID(A2,{1,3,5},1)*1)=0,COUNT(MID(A2,{2,4,6},1)*1)=3)