Forum Discussion

dinesh reddy's avatar
dinesh reddy
Copper Contributor
Dec 22, 2017

reg: pan characteristics

hello everyone, i have a doubt regarding which combination of functions to be used for verifying the characteristics of a pan,i.e.  every pan  has 4 numerics and 6 alphabets,so i need to verify it ...
Excel on mobile
Formulas & Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Dec 22, 2017

Hi dinesh,

 

Assuming your string has only numbers and alphabet characters, no other symbols, the formula could be

=AND(
   (SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))=4),
   ((LEN(A1)-SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))=6))
)

First part calculates counts number of numeric characters in the string, subtracting  it from the length of the string in second part with have number of alphabets. AND compares with 4 and 6 above numbers and returns TRUE/FALSE result.

 

Resources