Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Data Validation - 3 Letters + 2 Numbers

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Data Validation - 3 Letters + 2 Numbers

    Hi,

    Can someone please tell me where I am going wrong here?

    =AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(RIGHT(A62,2))))

    Thanks!

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,232
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by tlc53 View Post
    Can someone please tell me where I am going wrong here?

    =AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(0+RIGHT(A62,2))))
    The RIGHT function returns a Text value, not a numeric value, so ISNUMBER will not see the return from the RIGHT function as a number even if it is composed of all digits. However, if you involve a Text number in a mathematical operation, then Excel will convert the Text number to a real number in order to be able to complete the mathematical operation. So, if you add 0 or multiply by 1, you will convert the Text number to a real number so that ISNUMBER can see it as a number. So, making the change I show in red will get that part of your formula working correctly. I want to point out, however, that what I highlighted in blue is not guaranteed to return TRUE only for letters (what your thread title said you wanted)... the ISTEXT function will return TRUE for things like A12, 3B4, 56C, and the like. Also, since its argument is a Text function, ISTEXT will return TRUE even if the LEFT function returns all digits (see the first part of this response). You will need a different test to do what you want... let me think about it for a bit.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,232
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Give this formula a try...

    =AND(LEN(A1)=5,IF(MID("AAA00",{1,2,3,4,5},1)="A",(MID(A1,{1,2,3,4,5},1)>="A")*(MID(A1,{1,2,3,4,5},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5},1))))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by Rick Rothstein View Post
    the ISTEXT function will return TRUE for things like A12, 3B4, 56C, and the like. Also, since its argument is a Text function, ISTEXT will return TRUE even if the LEFT function returns all digits (see the first part of this response). You will need a different test to do what you want... let me think about it for a bit.
    Thank you very much for this!
    I think allowing A12, 3B4 etc, may actually be what I want. I will need to test it out. Unfortunately I'm now being dragged away from my laptop, so I will need to look at it later. Thanks again and for the explanation of where I was going wrong.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,232
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by tlc53 View Post
    I think allowing A12, 3B4 etc, may actually be what I want.
    Your thread title said 3 letters so that is what I responded to.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by Rick Rothstein View Post
    Your thread title said 3 letters so that is what I responded to.
    I thought 3 Letters + 2 Numbers was what I wanted but when I read your message, I realised I have an account G4S01. Didn't mean to be misleading, just an oversight which was made clear to me once I read your message. Sorry about that.
    For this reason, I think your first formula will be best for this purpose. However, it is saying there is a formula error when I try to enter =AND(LEN(A62)=5,ISTEXT(LEFT(A62,3),ISNUMBER(0+RIGHT(A62,2))))
    Can you perhaps see where I am going wrong?

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by tlc53 View Post
    I thought 3 Letters + 2 Numbers was what I wanted but when I read your message, I realised I have an account G4S01.
    Given the change, can you clearly re-state in words exactly what the DV requirements are?
    Last edited by Peter_SSs; Sep 23rd, 2019 at 06:14 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by Peter_SSs View Post
    Given the change, can you clearly re-state in words exactly what the DV requirements are?
    3 letters or numbers followed by 2 numbers. Thanks.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by tlc53 View Post
    3 letters or numbers followed by 2 numbers. Thanks.
    I have assumed that any letters must be upper case. If so, try this for your custom DV formula.

    =AND(LEN(A1)=5,OR(MAX(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:3")),1))))<13,ISNUMBER(-MID(A1,ROW(INDIRECT("1:3")),1))),ISNUMBER(-MID(A1,ROW(INDIRECT("4:5")),1)))
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation - 3 Letters + 2 Numbers

    Quote Originally Posted by Peter_SSs View Post
    I have assumed that any letters must be upper case. If so, try this for your custom DV formula.

    =AND(LEN(A1)=5,OR(MAX(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:3")),1))))<13,ISNUMBER(-MID(A1,ROW(INDIRECT("1:3")),1))),ISNUMBER(-MID(A1,ROW(INDIRECT("4:5")),1)))
    Wow, a lot more complicated than I had imagined. You were correct to assume the letters should be in caps. I tested it out and it works perfectly. Thanks so much

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •