Hi All,
I need to data validations for two different cells anyone able to assist
First cell needs to be in this format in numbers only
#####/##
Second needs to be a Letter followed by 6 numbers eg:
J123456
I have tried all types of formulas without any success
Format: #####/##
Select the cell where you want this validation (e.g., A1). Go to Data >> Data Validation. Set Allow to Custom. In the Formula box, enter this:
Formula:
=AND(LEN(A1)=8,ISNUMBER(VALUE(LEFT(A1,5))),MID(A1,6,1)="/",ISNUMBER(VALUE(RIGHT(A1,2))))
For letter followed by 6 numbers
Formula:=AND(LEN(A1)=7,ISTEXT(LEFT(A1,1)),ISNUMBER(VALUE(RIGHT(A1,6))))
Life unfolds as we shape it.
What version of Excel are you using?
If you have 365 with Regex functions how aboutandFormula:=REGEXTEST(D2,"\d{5}/\d{2}")Formula:=REGEXTEST(E2,"[A-Z]\d{6}")
Please add your Excel version to your profile. It won't help you to give you a solution that won't work with your version. Provide the commercial release name (e.g., Excel 2016, Excel 2024, Microsoft 365).Originally Posted by stu_C
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks