PAN Validation 01: MID, CODE, IF, AND function

Knowledge Series demonstration validation of PAN Number. This videos demonstrates basic character validation of PAN using MID, CODE, IF, AND functions of Excel. Office Online version of Excel is used in this video
To sign-up free account of Office Online, visit below website
www.office.com/

[@MM-qk4if]

Useful tips about data validation. 👍

[@karthikrajgopal8627]

Thanks sir, Very useful.. can't we use isnumber() to validate

[@ExcelKida]

Yes you can for individual digits, but issue is that MID function converts output to text (even if it is number), so you will have to again use VALUE function to convert it to NUMBER and the evaluate it using ISNUMBER, which will un-necessarily complicate logic. So, in order to keep logic uniform (and easy for copy-paste), I have used this approach of CODE function

[@aibelrapheal5176]

Hi sir , I switched to your method.. Better than using isnumber, istext function... Can we restrict 4th letter to only those characters which represents the type of assessee ?

[@ExcelKida]

Already demonstrates, please check out all the videos of series

[@aibelrapheal5176]

Sir, Can I use istext and isnumber functions instead of asci codes .. ? I am trying my method.. but it does not work

[@ExcelKida]

Output of MID function is always in text format, even if character extracted is a number of alphabet. So your approach of ISNUMBER, ISTEXT will not work

[@aibelrapheal5176]

@@ExcelKida got it sir... I was looking for solutions in the meantime ... I found solution... I used value function to convert the result of mid,left,right functions into text or numbers depending upon the case.. As I am using 2016 version , asci code function is not available ... So... ✌️

[@ExcelKida]

Yes you can use VALUE approach also. Objective of video is to demonstrate uniform & consistent approach which works for both Alphabet & Number. If you notice, in my approach I have even covered capital letter check via CODE. Also this is a series, where dynamic array concept is also demonstrated in next video. Checkout all the videos of this series (playlist)

[@MandarSant123]

how do I generate random pan card number in excel

[@ExcelKida]

Good suggestion, will surely think of video on it