Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 37

Thread: Allow only certain alphanumeric data in a range

  1. #21
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,483
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Adjusted to prevent space before number
    Code:
    =AND(COUNTIF($A:$A,A1)=1,OR(LEFT(A1,2)="AK",LEFT(A1,2)="OR"),--RIGHT(A1,LEN(A1)-2)>=1,ISNUMBER(--RIGHT(A1,LEN(A1)-2)),--RIGHT(A1,LEN(A1)-2)<=100,MID(A1,3,1)<>" ")
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  2. #22
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Special K99,
    I think we are close to the result

  3. #23
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Scott,
    updated the code to this, (only a1 changed to a8)
    Code:
    =AND(COUNTIF($A:$A,A8)=1,OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR"),--RIGHT(A8,LEN(A8)-2)>=1,ISNUMBER(--RIGHT(A8,LEN(A8)-2)),--RIGHT(A8,LEN(A8)-2)<=100,MID(A8,3,1)<>" ")
    However right now it is accepting any data in it
    Last edited by patni; May 16th, 2019 at 11:21 AM.

  4. #24
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,910
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Quote Originally Posted by patni View Post
    Hey Special K99,
    I think we are close to the result
    Badly worded description of what you wanted, sounded like you wanted "OR 50"

    Try this, avoids spaces, must begin AK or OR, end with a number from 1-100, and not be duplicated from A1:A1000

    =AND(ISNUMBER(SEARCH(" ",A99)),OR(LEFT(A99,2)="AK",LEFT(A99,2)="OR"),RIGHT(A99,LEN(A99)-2)+0>=1,RIGHT(A99,LEN(A99)-2)+0<=100,COUNTIF(A$1:A$1000,A99)=1)

  5. #25
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,672
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    10 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Are you sure the code is running and/Or your "Application.EnableEvents" are set to true

  6. #26
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey SPecial K99,

    I used the above code,

    However strangely it is accepting any data in it.

    Example : i wrote "HT1" and it accepted

  7. #27
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,910
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Quote Originally Posted by patni View Post
    Hey SPecial K99,

    I used the above code,

    However strangely it is accepting any data in it.

    Example : i wrote "HT1" and it accepted
    My fauilt, wrong way round, need a NOT on the space check

    Try

    =AND(NOT(ISNUMBER(SEARCH(" ",A99))),OR(LEFT(A99,2)="AK",LEFT(A99,2)="OR"),RIGHT(A99,LEN(A99)-2)+0>=1,RIGHT(A99,LEN(A99)-2)+0<=100,COUNTIF(A$1:A$1000,A99)=1)
    Last edited by Special-K99; May 16th, 2019 at 11:51 AM.

  8. #28
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Hey Special K99,
    I changed the code to this (changed a99 to a8)
    It works perfectly now. It even prevents duplicate entries.

    Thank you so much Special K 99


    Code:
    =AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$8:A$1000,A8)=1)

  9. #29
    New Member
    Join Date
    Jul 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    THANK YOU EVERYONE

    thank you for all the help, knowledge, time and patience.

    Thank you so much

  10. #30
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,585
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    31 Thread(s)

    Default Re: How to allow only certain alphanumeric data in a range

    Quote Originally Posted by patni View Post
    Hey Special K99,
    I changed the code to this (changed a99 to a8)
    It works perfectly now. It even prevents duplicate entries.
    Just so you know, Data Validation will not prevent someone from copying bad data from a different location and pasting into the "protected" cell.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •