• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom Data Validation for Alpha-Numeric Formatting

R

rsomist

Member
I am trying to use Data Validation to limit what goes into a cell. I have a specific format that must be followed and I've tried creating a function using ISNUMBER, AND, SEARCH, IF, and SUMPRODUCT and am just lost with no success. I'm not even sure it's possible, so any help would be much appreciated. Here are the rules of the format:

1. No spaces can be used

2. It must start with 8 numbers

3. The 9th character will either be an M, and W, or an H

4. If the 9th character is an M, then that will be the last character. Example - 12995678M

5. If the 9th character is an F, then up to 6 additional characters can be used. Examples - 12344578WMaggie or 11045678WSam or 12338678WSarah or 29445678WAllie (no spaces)

6. If the 9th character is an H, then up to 6 additional characters can be used. Examples - 12983647HBob or 99384756HRobert or 33948557HJeffry or 22938475HScott (no spaces)

7. There will always be at least 9 total characters (the 8 leading numbers and at least the M).

There can never be more than 15 total characters (the 8 leading numbers, the M/W/H, plus the 6 characters).

Thank you so much!
 
I really don't recommend using data validation formula for validating entry in cell. Since, user can use copy/paste to over-write validation rule.

At any rate, can you clarify following?
5. If the 9th character is an F, then up to 6 additional characters can be used.
Click to expand...

Looking at your examples... do you mean "W"?
 
Yes, I do - the 9th character options would either be M, W, or H... there shouldn't be an F in the 9th character spot. Sorry for the confusion.

Chihiro said:
I really don't recommend using data validation formula for validating entry in cell. Since, user can use copy/paste to over-write validation rule.

At any rate, can you clarify following?


Looking at your examples... do you mean "W"?
Click to expand...
 
Use following validation formula.
=(OR(MID(A2,9,1)={"W","H"})*(LEN(A2)<16)+(MID(A2,9,1)="M")*(LEN(A2)=9))*(LEN(A2)=LEN(SUBSTITUTE(A2," ","")))*(ISNUMBER(LEFT(A2,8)+0))
 
Oh right, just change OR(MID(A2,9,1)={"W","H"}) portion to...
OR(MID(A2,9,1)="W",MID(A2,9,1)="H")

Forgot that you can't use array constants in Validation formula.
Type formula with above change into custom validation formula.
 
Hmmm - I pasted this into the data validation and it's restricting my entries, even when they follow the correct format. I pasted this:

OR(MID(A2,9,1)="W",MID(A2,9,1)="H")*(LEN(A2)<16)+(MID(A2,9,1)="M")*(LEN(A2)=9))*(LEN(A2)=LEN(SUBSTITUTE(A2," ","")))*(ISNUMBER(LEFT(A2,8)+0))

And I tried using 12345678M and 12345678WSarah, and both were denied even though they follow the correct format?
 
Hi, to all!

Try this formulae into DV (Data Validation):
=(LEN(A2)<16)*LEFT(A2,8)*(A2=TRIM(A2))*(SEARCH(MID(A2,9,1),"MWH")+(MID(A2,9,1)="M")*(LEN(A2)=9))

P.D: Be sure that your active cell is A2 when you make this validation.

Blessings!
 
These are great! Thank you so much for your help! Now, if I need to do the entire A column, should I change the A in the equation to an A:A?

Thanks
 
No, since it's relative reference. As you copydown the validation below A2. It will change to appropriate reference.
 
You must log in or register to reply here.
Back
Top