Page 4 of 4 FirstFirst ... 234
Results 31 to 37 of 37

Thread: Allow only certain alphanumeric data in a range

  1. #31
    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 Rick Rothstein,
    I just checked that.

    Okay this is something intresting. Is there any ways to prevent this ?
    Just very curious
    Last edited by patni; May 16th, 2019 at 12:10 PM.

  2. #32
    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 K 99,

    Is it possible to add 2 more criteria in the code ? (Sorry for the trouble)
    I mean we have "AK" and "OR" in the code below.

    Is it possible to add "LE" and "LK" as well?

    So total we have 4 criteria "AK" "OR" "LE" "LK"

    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
    Last edited by patni; May 16th, 2019 at 12:22 PM.

  3. #33
    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

    I tried this but something is wrong I guess. could you check please.
    THANK YOU SO MUCH

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

  4. #34
    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 Rick Rothstein,
    I just checked that.

    Okay this is something intresting. Is there any ways to prevent this ?
    Just very curious
    The only way to have fool-proof data validation is by using properly designed Change event coding.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #35
    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
    I tried this but something is wrong I guess. could you check please.
    THANK YOU SO MUCH

    Code:
    =AND(NOT(ISNUMBER(SEARCH(" ",A8))),OR(LEFT(A8,2)="AK",LEFT(A8,2)="LE",LEFT(A8,2)="LK",LEFT(A8,2)="OR"),RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0>=1,RIGHT(A8,LEN(A8)-2)+0<=100,COUNTIF(A$8:A$1000,A8)=1
    You've deleted a comma after the second RIGHT, see highlighted

  6. #36
    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

    And you've deleted the closing brackets and changed the COUNTIF formula so it doesnt work
    This is what it should be

    =AND(NOT(ISNUMBER(SEARCH(" ",A8))),(OR(LEFT(A8,2)="LE",OR(LEFT(A8,2)="LK",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$1:A$1000,A8)=1)

    If you're gonna change cell references in this formula then only change the references to cell A8 - NO OTHER CELL REFERENCES or you're undoing the work I've done for you.
    Last edited by Special-K99; May 17th, 2019 at 04:41 AM.

  7. #37
    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 ,
    Sure.
    Thank you so much for the help.
    I checked my mistake and hopefully i learn from it.

    The code works perfectly now.
    Thank you for the help

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
  •