+ Reply to Thread
Results 1 to 11 of 11

Cell Validation specific Alpha Numeric format returns false if in wrong format

  1. #1
    Registered User
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    79

    Cell Validation specific Alpha Numeric format returns false if in wrong format

    Hi,

    Been googling for the last three hours with no joy.

    What I am trying to achieve is that when a user enters a value in cell d3 it tells them they can't if it is not in the correct format

    The format is as follows:

    AS-1234-5678-9111

    AA-NNNN-NNNN-NNNN

    Also the first two letters will always be AS if it makes it any easier?

    Any suggestions as I didn't realise it would be this difficult?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Does this work?

    =AND(LEFT(D31,2)="AS",MID(D3,4,4)+0>0,MID(D3,4,4)+0<=9999,MID(D3,9,4)+0>0,MID(D3,9,4)+0<=9999,MID(D3,14,4)+0>0,MID(D3,14,4)+0<=9999)
    Custom Data Validation formula

    Note that if they copy and paste into the cell Data Validation will not trigger and will accept what they enter (someone told me recently).

    Are they inputting hyphens as well ?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    I interpreted the request differently.

    Assuming that you only want to allow strings with the following parameters:
    1) Alphanumeric with only capital letters
    2) Hyphens in character positions 3, 8, and 13
    3) 17 character long
    4) Starting with "AS"

    Try this:

    Please Login or Register  to view this content.
    Edit: Just realized that this exceeds the formula character limit for DV formulas.
    Working on a fix.
    Last edited by 63falcondude; 07-11-2019 at 12:54 PM.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    I just realized that I misunderstood the request and was making it way more complex than it needed to be.

    Assuming these are the parameters:
    1) Starts with AS
    2) Hyphens at places 3, 8, and 13
    3) 17 characters
    4) Only numbers after "AS" (not including hyphens)

    You can use this DV formula:
    =AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(D3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

    See attachment.
    Attached Files Attached Files
    Last edited by 63falcondude; 07-12-2019 at 07:45 AM.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Quote Originally Posted by 63falcondude View Post
    I just realized that I misunderstood the request and was making it way more complex than it needed to be.

    Assuming these are the parameters:
    1) Starts with AS
    2) Hyphens at places 3, 8, and 13
    3) 17 characters
    4) Only numbers after "AS" (not including hyphens)

    You can use this DV formula:
    =AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(E3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

    See attachment.
    Typo you have LEN(E3)=17

    I was wondering about leading zeroes in each 4-digit number but you've covered that well!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Thanks Special-K! Fixed.

  7. #7
    Registered User
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    79

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Works perfectly 63falcondude and thanks for your input Special-K.

    Many thanks

    Can I ask out of interest (not that it is relevant to this formula) if I wanted to say the first two letters must contain text and not fix it to AS how would I manipulate this part of the formula

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    First thought

    AND(LEFT(D3,1)>="A",LEFT(D3,1)<="Z",MID(D3,2,1)>="A",MID(D3,2,1)<="A")

  9. #9
    Registered User
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    79

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    I'm assuming the last <="A" should be <="Z"

    But it works perfectly.

    Thank you both

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Here's another way:

    =SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW($65:$90)),LEFT(D3,2))))=2

    Glad we could help. Thanks for the rep!

    Edit: The rows should be surrounded by INDIRECT to prevent this from changing if rows are deleted in the workbook.
    That would look like this:

    =SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),LEFT(D3,2))))=2
    Last edited by 63falcondude; 07-12-2019 at 10:06 AM.

  11. #11
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Cell Validation specific Alpha Numeric format returns false if in wrong format

    Quote Originally Posted by Ratso View Post
    I'm assuming the last <="A" should be <="Z"

    But it works perfectly.

    Thank you both
    Yep that's right
    My glasses need cleaning

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Determine if Cell Format is M/D/YYYY and Change to Numeric Value in YYYYMMDD Format
    By bryanmarks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2017, 06:16 PM
  2. Effectivity Validation by searching Numeric# in Text format cells
    By CAABYYC in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2016, 04:00 PM
  3. Convert dates in 'dd/MM/yy' format to 'MMM-YY'. Excel returns wrong year
    By luis_vxd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-13-2016, 01:34 AM
  4. Format column with both alpha and numeric characters
    By wsykes41770 in forum Excel General
    Replies: 3
    Last Post: 10-01-2014, 11:15 AM
  5. Data validation - alpha-numeric
    By nfn in forum Excel General
    Replies: 1
    Last Post: 07-11-2012, 06:33 AM
  6. Conditional format a cell when the formula returns False
    By eddienole in forum Excel General
    Replies: 1
    Last Post: 05-16-2012, 01:35 PM
  7. Replies: 4
    Last Post: 05-19-2011, 05:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1