+ Reply to Thread
Results 1 to 5 of 5

Alphanumeric Data Validation, Input Mask?

  1. #1
    Registered User
    Join Date
    11-28-2013
    Location
    Excelland
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Alphanumeric Data Validation, Input Mask?

    Hello,

    I'm trying to do the following as a formula for data validation.

    The string that should be allowed in cell A1 is:

    ABCnnnnnnLLLXX

    ABC is the string "ABC"
    n is a number
    L is a capital letter
    X is either a number or a capital letter


    As data validation formula, I came up with the following formula:

    Please Login or Register  to view this content.

    My problem now is that the CODE function only looks at the first character of the selected area defined by the MID functions.

    So far I can still enter strings like

    ABCnnnnnnL%%X%

    where % is any character

    e.g. ABC123456A$*0


    Is there a function similar to the CODE function or is there an easier way of solving my problem?


    Any help will be appreciated.

    Kind regards
    bondingfortoday
    Last edited by bondingfortoday; 01-20-2014 at 10:32 PM. Reason: Code brackets

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,698

    Re: Alphanumeric Data Validation: Problem with CODE Function

    You have to evaluate each characters in your formula

    Just confirm you are looking data validation for this

    ABCnnnnnnLLLXX

    Or

    ABCnnnnnnL%%X%


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Alphanumeric Data Validation: Problem with CODE Function

    Try this for validation of A1
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-28-2013
    Location
    Excelland
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Alphanumeric Data Validation: Problem with CODE Function

    Thanks very much for your replies.

    I would like the data validation to only allow this format:

    ABCnnnnnnLLLXX

    kvsrinivasamurthy's formula refers to rows whereas I'm just trying to refer to the 10th to the 12th and 13th to the 14th character of the cell A1.

  5. #5
    Registered User
    Join Date
    11-28-2013
    Location
    Excelland
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Input Mask?

    Is there a functionality within Excel that lets you define input masks regarding this problem?

+ 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. alphanumeric data validation
    By pankajy18 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2013, 01:07 PM
  2. Problem with Data Validation VBA code and protected sheets. Please help.
    By drew.j.harrison in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2013, 01:33 AM
  3. VBA code silently crashing. Problem with Data Validation drop down lists.
    By Don Wiss in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-28-2012, 10:01 PM
  4. alphanumeric Data Validation across tabs in a workbook
    By CharterJP in forum Excel General
    Replies: 17
    Last Post: 11-19-2010, 03:19 PM
  5. custom data validation for alphanumeric entry
    By wotadude in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2009, 04:23 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