LinkBack URL
About LinkBacks
I'm struggling with some data validation which I'm sure is pretty simple but any help would be appreciated.
I want the validation to restrict only 24 characters 9 numbers/14numbers e.g. 123456789/12345678901234
Thanks again
I'm struggling with some data validation which I'm sure is pretty simple but any help would be appreciated.
I want the validation to restrict only 24 characters 9 numbers/14numbers e.g. 123456789/12345678901234
Thanks again
Try this for your data validation formula, replacing A3 with the actual cell reference:
Formula:
Please Login or Register to view this content.
Last edited by 6StringJazzer; 02-10-2014 at 04:01 PM. Reason: Cell ref in description didn't match formula, updated description
@ 6StringJazzer,
This is a hard one for me. Your formula works partially since it wont limit the Right Value to 14 characters only.
I am having a hard time trying to figure this out.
Kind Regards.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Oh, good point, then:
Formula:Please Login or Register to view this content.
@ 6StringJazzer,
Note: Got your name correct this time.
I was just about to suggest the "Len" function as, =IF(LEN(A3)<>24,TRUE), and just that.
Why bother with the rest?
Kind Regards.
@ 6StringJazzer,
Sorry, the formula should read: =IF(LEN(A3)=24,TRUE,FALSE)
Last edited by Winon; 02-10-2014 at 04:33 PM. Reason: Corrected Cell reference
As I understand the problem, you want no more than 9 numbers to the left of / and no more than 14 to the right of /. I think that this will do what you want.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
@ newdoverman,
Your formula does not seem to work with DV in the actual cell.
Kind Regards
Oops! Wasn't meant for DV but column next to numbers.....should have stated that.
I can't figure a way to get DV to accept a double criteria as requested.
Well, because it works. It ensures that all criteria specified by the OP are included in the validity check. I tested it in the attached.Originally Posted by Winon
Notwithstanding newdoverman's solution, I interpreted the OP as:
Exactly 9 digits followed by the character "/" followed by exactly 14 digits.
Maybe I didn't understand your question, but there are several things to check besides just the overall length. By the way, in your second formula, the expression
Formula:Please Login or Register to view this content.
is correct but is equivalent to
Formula:Please Login or Register to view this content.
It is not necessary to use IF to produce a Boolean when the expression you are testing already returns a Boolean.
I would be very interested to have Ted Dennis return to tell us if any of this solves his problem....
@ 6StringJazzer,
Yes you are correct, your amended formula with the Len function, works like a charm.
I had a couple of cells to experiment with various options, and in a moment of "weakness", I got totally carried away, and drifted off the actual requirements.
Sorry about that!
Kind regards.
Sorry for the time its taken to get back to you guys. Firstly thank you for everyones input. Having read through the thread the first formula I tried was 6String Jazzer's -
=AND(LEN(A3)=24,ISNUMBER(VALUE(LEFT(A3,9))),ISNUMBER(VALUE(RIGHT(A3,14))),MID(A3,10,1)="/")
This has worked exactly as required.
To clarify the DV was for 9 characters followed by a "/" followed by another 14 characters
Thanks again
Many Thanks
You're welcome! Thanks for the rep, and thanks for marking your thread Solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks