• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel Sheets Input Only PAN CARD Number
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: 129)
Hello Mohit,

Please find required result attachment.

Vijay Kumar Smile

Attached Files
.xlsx   Pan card Format.xlsx (Size: 9.98 KB / Downloads: 93)
(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.
Hi Mohit

Good morning please fined required result without sumproduct. Smile 

Vijay Kumar


Attached Files
.xlsx   Pan card Format(1).xlsx (Size: 10.03 KB / Downloads: 52)
.xlsx   Pan card Format(1).xlsx (Size: 10.03 KB / Downloads: 32)
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..
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...


Vijay Kumar Smile

Attached Files
.xlsx   Pan card answer.xlsx (Size: 12.64 KB / Downloads: 57)
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

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

Forum Jump:

Users browsing this topic: 1 Guest(s)