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 using a function whether the pan has 4 numerics and 6 alphabets.

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