+ Reply to Thread
Results 1 to 5 of 5

Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

  1. #1
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

    I want to restrict data entry in a column to only accept data in the cell if it contains ****XY*** in this position. For example, this is expected to be a fixed length cell entry of 9 charters but the letters XY must be in the position shown i.e. 5th & 6th characters of the 9 character entry. A warning would be good to tell users when this requirement is not satisfied; and or have these required characters auto filled.

    Thanks JET

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

    Re: Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

    hi JET2011. select the cells you want the dropdown applied to (say A1:A5). go to Data -> Data Validation -> Allow: Custom -> Formula:
    =MID(A1,5,2)="XY"

    or if you want a fixed 9 characters too, then:
    =AND(MID(A1,5,2)="XY",LEN(A1)=9)

    at the same Validation window, you can go to the "Error Alert" tab & show a warning. the "Input Message" tab can be used for instructions to users

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

    Worked perfect! Solved. Thanks

  4. #4
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

    Opps UNSOLVED. Got the formula to work nicely on a single cell but can't get it to work across a range of cells in a column. I even tried enter the formula directly in a cell and drag it down to copy the formatting but no luck.

    Here is my formulas I put into the custion validation: It only worked for cell B12

    =AND(MID(B12-B20,5,2)="AA",LEN(B12-B20)=9)

    =AND(MID(B12:B20,5,2)="AA",LEN(B12:B20)=9)

  5. #5
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    Excel 2010 Ver. 14
    Posts
    215

    Re: Restrict data entry - data validation - mask - sandwiched data in a cell must be XY

    Solved (again)

    I really have it this time, thanks again to Benishiryo. (can't figure out how to PM)

    HTML Code: 
    =AND(MID(A1,5,2)="AA",LEN(A1)=9)
    The key for me was to select the area you desired to have validated then enter the custom validation for the first cell only; no need to put the range like I was attempting to do.

    This site is the best.

    J
    Last edited by JET2011; 02-02-2013 at 04:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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