Quote Originally Posted by in.vaibhav View Post
Hi

There is one more requirement, 4Th Character should be P for Individulas, C for Company & so on.

So how can we check that & also whether we can give output with error instead of say True or False.

EG. Length should be 10, First 5 character should be text, Next 6-9 character should be number, Last character should be text, 4th character should be either of P,H,F,A,T,B,L,J,G.

Thanks & Regards
Try This...

=IF(AND(OR(MID(A1,4,1)={"P";"H";"B";"C";"L";"F";"A";"J";"G";"T"}),CODE(UPPER(MID(A1,{1,2,3,4,5},1)))>=65,CODE(UPPER(MID(A1,{1,2,3,4,5},1)))<=90,CODE(LOWER(MID(A1,{10},1)))>=97,CODE(LOWER(MID(A1,{10},1)))<=122,CODE(MID(A1,{6,7,8,9},1))>=49,CODE(MID(A1,{6,7,8,9},1))<=57,LEN(A1)=10),"VALID","INVALID")