• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel Sheets Input Only PAN CARD Number
#1
Hi Fih Team,

I want to a data validation in Excel sheet Pan Card Number Format. I put only Pan Card Number, below details pan card number format.

• Length should be 10
• First 5 character should be text - [A-Z]
• Next 4 should be number
• Last character should be text - [A-Z]


Attached Files
.xlsx   Pan Card Number (Data Validation).xlsx (Size: 7.66 KB / Downloads: 124)
  Reply
#2
Hello Mohit,


Please find required result attachment.



Vijay Kumar Smile


Attached Files
.xlsx   Pan card Format.xlsx (Size: 9.98 KB / Downloads: 89)
  Reply
#3
(05-Oct-2017, 10:28 PM)Vijay Kumar Wrote: Hello Mohit,


Please find required result attachment.



Vijay Kumar Smile


Hi Vijay,

Thank you very much for giving the solution but i want to solution this query without Sumproduct Function because sumproduct function it's not proper understand and confusing any array formula.

so plz giving the solution without Sumproduct Function.
  Reply
#4
Hi Mohit



Good morning please fined required result without sumproduct. Smile 


Vijay Kumar

PFA....


Attached Files
.xlsx   Pan card Format(1).xlsx (Size: 10.03 KB / Downloads: 50)
.xlsx   Pan card Format(1).xlsx (Size: 10.03 KB / Downloads: 31)
  Reply
#5
Hi Vijay,

Good Morning, this formula not working because if you input number for example (abcde23aa1) or (1111111111) then it is taken it's only working 10 digit length basis otherwise plz check again the formula..
  Reply
#6
Good morning everyone,



Mohit you were right, I checked that formula was not working, However, I have created a formula that will restrict you to enter any other text apart from Pan card Number. It was really difficult for me but i did it. Smile  Thanks anyway for such a good question.



This formula may look lengthy but fulfill your requirement. or haa don't need (SUMPRODUCT) PFA...

=IF(AND(MID(G16,1,1)<>"",MID(G16,2,1)<>"",MID(G16,3,1)<>"",MID(G16,4,1)<>"",MID(G16,5,1)<>"",MID(G16,10,1)<>""),AND(COUNT(FIND(MID(G16,1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1,COUNT(FIND(MID(G16,2,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1,COUNT(FIND(MID(G16,3,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1,COUNT(FIND(MID(G16,4,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1,COUNT(FIND(MID(G16,5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1,COUNT(MID(G16,6,1)+0)=1,COUNT(MID(G16,7,1)+0)=1,COUNT(MID(G16,8,1)+0)=1,COUNT(MID(G16,9,1)+0)=1,COUNT(FIND(RIGHT(G16,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=1))




Vijay Kumar Smile


Attached Files
.xlsx   Pan card answer.xlsx (Size: 12.64 KB / Downloads: 54)
  Reply
#7
Hi Vijay,

Sorry for late reply i am proud in part of Fih always New Innovation every time. I really appreciate for you for taking time in Data Validation query. your last reply in really helpful but I request you please solve this query in short formula because this formula is very lengthy i belive you can do this. Smile
  Reply


Related Topics
Topic Author Replies Views Last Post
  Number Format Number format mr.dilip 1 1,204 25-Mar-2019, 02:27 PM
Last Post: Vivekananda Sinha
  Formula Count the number in one cell Amit Dhankar 4 2,286 10-Feb-2019, 12:10 AM
Last Post: Amit Dhankar
  Formula Convert Text Into Number & Calculate Formula With "RS" @m!t $@!n! 3 2,236 21-Aug-2018, 12:26 PM
Last Post: devendra_dev20
  Data Filtering Query: Vlookup: Extract Number From Data @m!t $@!n! 3 2,297 10-Aug-2018, 10:53 PM
Last Post: @m!t $@!n!
  Formula Number of shift poulates against the number Inderjeet Pal 1 1,726 09-Jul-2018, 10:17 AM
Last Post: Vivekananda Sinha
Date Hyperlink Index of sheets with Hyperlink Harshi Gupta 2 2,061 27-Jun-2018, 05:59 PM
Last Post: Vivekananda Sinha
  Number Format Number Format Amit Dhankar 1 1,690 04-Jun-2018, 05:24 PM
Last Post: JatinVats
  Number Format Number format Sanjeev Singh 2 2,288 06-Nov-2017, 02:16 AM
Last Post: Sanjeev Singh
  Number Format Excel Number formatting issue Yogesh kumar 1 2,184 05-Oct-2017, 11:04 PM
Last Post: Mohit Sharma
  Formula Get phone number from a text string Dayal Singh 1 2,360 18-Jul-2017, 08:59 PM
Last Post: Lovedeep sharma
  Formula Prefix Add - Number & Character Sachin Kant 1 2,103 10-Jul-2017, 12:18 PM
Last Post: Vivekananda Sinha

Forum Jump:


Users browsing this topic: 1 Guest(s)