Specific cell formatting with validation check
- Thread starter Stumpy
- Start date
Faseeh
Excel Ninja
Hi Stumpy,
Welcome to the Forums!!!
Lets say that you want to apply this validation to Cell D6.
1. Go to the Data Validation
2. Select Allow > "Custom"
Enter this formula in the space and Press Ok
Hope that works!!
Regards,
Faseeh
Welcome to the Forums!!!
Lets say that you want to apply this validation to Cell D6.
1. Go to the Data Validation
2. Select Allow > "Custom"
Enter this formula in the space and Press Ok
Code:
AND(LEN(D6)=6,(MID(D6,3,1)="/")=TRUE,ISERROR(VALUE(LEFT(D6,2)))=FALSE,ISERROR(VALUE(RIGHT(D6,3)))=FALSE)Hope that works!!
Regards,
Faseeh
Faseeh,
Thank you for the reply. I tried it in D6 as a custom data validation but it did not work. It came up with an error. I want to input say '12/345'. This means there will be 6 keystrokes to input the cell value, not a case of inputting 12345 and then it being changed to 123/45. I hope you understand my explanation!!!!!!!! Thank you.
Thank you for the reply. I tried it in D6 as a custom data validation but it did not work. It came up with an error. I want to input say '12/345'. This means there will be 6 keystrokes to input the cell value, not a case of inputting 12345 and then it being changed to 123/45. I hope you understand my explanation!!!!!!!! Thank you.
Stumpy,
When you pasted Faseeh's formula above, did you by chance add an equals sign before his code? In the formula box, the text should actually read:
=AND(LEN(D6)=6,(MID(D6,3,1)="/")=TRUE,ISERROR(VALUE(LEFT(D6,2)))=FALSE,ISERROR(VALUE(RIGHT(D6,3)))=FALSE)
If you happened to paste it without first adding the equals sign, Excel won't know it's a formula and think that only the text "AND(LEN(D6)=6,(MID(D6,3,1)="/")=TRUE,ISERROR(VALUE(LEFT(D6,2)))=FALSE,ISERROR(VALUE(RIGHT(D6,3)))=FALSE)" is a valid input. I hope that makes sense. It might also explain why the formula didn't work for you.
When you pasted Faseeh's formula above, did you by chance add an equals sign before his code? In the formula box, the text should actually read:
=AND(LEN(D6)=6,(MID(D6,3,1)="/")=TRUE,ISERROR(VALUE(LEFT(D6,2)))=FALSE,ISERROR(VALUE(RIGHT(D6,3)))=FALSE)
If you happened to paste it without first adding the equals sign, Excel won't know it's a formula and think that only the text "AND(LEN(D6)=6,(MID(D6,3,1)="/")=TRUE,ISERROR(VALUE(LEFT(D6,2)))=FALSE,ISERROR(VALUE(RIGHT(D6,3)))=FALSE)" is a valid input. I hope that makes sense. It might also explain why the formula didn't work for you.
Rewriting Faseeh's formula for clarity:
=AND(LEN(D6)=6,MID(D6,3,1)="/",ISNUMBER(VALUE(LEFT(D6,2))),ISNUMBER(VALUE(RIGHT(D6,3))))
The 4 checks are:
Is the string 6 characters long?
Is the 3rd character a forward slash?
Are the first 2 characters a number?
Are the last 3 characters a number?
=AND(LEN(D6)=6,MID(D6,3,1)="/",ISNUMBER(VALUE(LEFT(D6,2))),ISNUMBER(VALUE(RIGHT(D6,3))))
The 4 checks are:
Is the string 6 characters long?
Is the 3rd character a forward slash?
Are the first 2 characters a number?
Are the last 3 characters a number?
SirJB7
Excel Rōnin
@Jordan, Luke M
Hi!
I did this:
- formatted cell A1 as text
- entered Jordan's formula in cell B1
- entered Luke M's formula in cell C1
- typed "12/345" (unquoted) in cell A1
- retrieved both FALSE in B1:C1
Am I doing something wrong?
Regards!
PS: Just for the records... who in the hell was supposing you were using cell D6 instead of A1???
EDITED:
PS2: I didn't like something from the beginning and remembered that once got into same error, which now I don't realize what it was... But now yes!
The above formulas don't work for this case:
"1 /34 " (unquoted)
as Excel retrieves as number a string with trailing and leading spaces from a VALUE function.
So I propose to change the condition to:
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";NO(ESTEXTO(IZQUIERDA(A1;2)));NO(ESTEXTO((DERECHA(A1;3))))) -----> in english: =AND(LEN(A1)=6,MID(A1,3,1)="/",NOT(ISTEXT(LEFT(A1,2))),NOT(ISTEXT((RIGHT(A1,3)))))
Regards!
PS3: Yeah, I know I used A1
EDITED: It doesn't work for correct values, I think I should include LEN somewhere... be back in a while.
Back...
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";ESNUMERO(VALOR(IZQUIERDA(A1;2)));LARGO(ESPACIOS(IZQUIERDA(A1;2)))=2;ESNUMERO(VALOR(DERECHA(A1;3)));LARGO(ESPACIOS(DERECHA(A1;3)))=3) -----> in english: =AND(LEN(A1)=6,MID(A1,3,1)="/",ISNUMBER(VALUE(LEFT(A1,2))),LEN(TRIM(LEFT(A1,2)))=2,ISNUMBER(VALUE(RIGHT(A1,3))),LEN(TRIM(RIGHT(A1,3)))=3)
I think it works now. It isn't the way I did it that time but I don't remember how... but if works now, who cares? Does it work?
Regards!
Hi!
I did this:
- formatted cell A1 as text
- entered Jordan's formula in cell B1
- entered Luke M's formula in cell C1
- typed "12/345" (unquoted) in cell A1
- retrieved both FALSE in B1:C1
Am I doing something wrong?
Regards!
PS: Just for the records... who in the hell was supposing you were using cell D6 instead of A1???
EDITED:
PS2: I didn't like something from the beginning and remembered that once got into same error, which now I don't realize what it was... But now yes!
The above formulas don't work for this case:
"1 /34 " (unquoted)
as Excel retrieves as number a string with trailing and leading spaces from a VALUE function.
So I propose to change the condition to:
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";NO(ESTEXTO(IZQUIERDA(A1;2)));NO(ESTEXTO((DERECHA(A1;3))))) -----> in english: =AND(LEN(A1)=6,MID(A1,3,1)="/",NOT(ISTEXT(LEFT(A1,2))),NOT(ISTEXT((RIGHT(A1,3)))))
Regards!
PS3: Yeah, I know I used A1
EDITED: It doesn't work for correct values, I think I should include LEN somewhere... be back in a while.
Back...
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";ESNUMERO(VALOR(IZQUIERDA(A1;2)));LARGO(ESPACIOS(IZQUIERDA(A1;2)))=2;ESNUMERO(VALOR(DERECHA(A1;3)));LARGO(ESPACIOS(DERECHA(A1;3)))=3) -----> in english: =AND(LEN(A1)=6,MID(A1,3,1)="/",ISNUMBER(VALUE(LEFT(A1,2))),LEN(TRIM(LEFT(A1,2)))=2,ISNUMBER(VALUE(RIGHT(A1,3))),LEN(TRIM(RIGHT(A1,3)))=3)
I think it works now. It isn't the way I did it that time but I don't remember how... but if works now, who cares? Does it work?
Regards!
SirJB7
Excel Rōnin
@b(ut)ob(ut)hc
Hi!
Good evening, my friend.
I'm as calm as a two minute poured beer's foam.
It just happened that I looked at the formula and my nose said "Warning!". As it only said that and gave no clue I copied formulas and didn't work, obviously if D6 is not A1 (BTW Faseeh... why not starting at XYZ1234567?). I hit enter and realized my mistake and delete the post. But (and there's always a but, you know?) something was still smelling not so nice. then I remembered how an artful user broke my b..., my data validation, I mean, with the embedded spaces, and as I didn't remember the workaround used I decided to test the wrong condition. Missed something? Yes, the right condition. So post, repost, edit, reedit, ... I need a Carlsberg, do you join me?
Regards!
Hi!
Good evening, my friend.
I'm as calm as a two minute poured beer's foam.
It just happened that I looked at the formula and my nose said "Warning!". As it only said that and gave no clue I copied formulas and didn't work, obviously if D6 is not A1 (BTW Faseeh... why not starting at XYZ1234567?). I hit enter and realized my mistake and delete the post. But (and there's always a but, you know?) something was still smelling not so nice. then I remembered how an artful user broke my b..., my data validation, I mean, with the embedded spaces, and as I didn't remember the workaround used I decided to test the wrong condition. Missed something? Yes, the right condition. So post, repost, edit, reedit, ... I need a Carlsberg, do you join me?
Regards!
SirJB7
Excel Rōnin
@Jordan
Hi!
I know it, of course. Everybody after Faseeh took his formula to improve it, including me.
Nothing better than a Carlsberg for de-exhausting
Regards!
@Faseeh
Hi!
Do you realize what you've started?
Regards!
PS: no, I don't mean the improved formulas... three of the six (49.9999999999998% if we ask Excel) voices here are now drinking a beer... I actually don't know how to thank you, as you don't drink nothing with alcohol...
Hi!
I know it, of course. Everybody after Faseeh took his formula to improve it, including me.
Nothing better than a Carlsberg for de-exhausting
Regards!
@Faseeh
Hi!
Do you realize what you've started?
Regards!
PS: no, I don't mean the improved formulas... three of the six (49.9999999999998% if we ask Excel) voices here are now drinking a beer... I actually don't know how to thank you, as you don't drink nothing with alcohol...
@SirJB7
Having a little too much fun with the edit feature I think. =)
Thinking about, since there's only 5 characters to check, could just do this:
=AND(LEN(A1)=6,MID(A1,3,1)="/",AND(ISNUMBER(VALUE(MID(A1,{1,2,4,5,6},1)))))
Changes the checks to a more accurate representation of OP requirements
Is the string 6 characters long?
Is the 3rd character a forward slash?
Are the 1st, 2nd, 4th, 5th, and 6th characters all numbers?
@Stumpy
Yes, you can apply the save validation to multiple cells. Either select the range of cells and then apply with validation with the cell reference referring to the currently active cell, OR apply the validation to a single cell, and then select the larger range and extend the validation.
Having a little too much fun with the edit feature I think. =)
Thinking about, since there's only 5 characters to check, could just do this:
=AND(LEN(A1)=6,MID(A1,3,1)="/",AND(ISNUMBER(VALUE(MID(A1,{1,2,4,5,6},1)))))
Changes the checks to a more accurate representation of OP requirements
Is the string 6 characters long?
Is the 3rd character a forward slash?
Are the 1st, 2nd, 4th, 5th, and 6th characters all numbers?
@Stumpy
Yes, you can apply the save validation to multiple cells. Either select the range of cells and then apply with validation with the cell reference referring to the currently active cell, OR apply the validation to a single cell, and then select the larger range and extend the validation.
SirJB7
Excel Rōnin
@Luke M
Hi!
Ha ha ha... how did you guess it? Magician?
Actually a much nicer formula, pity that works on D6 instead of A1, but I think I can manage to handle it
Regards!
PS: if somebody wants to know how it becomes on an Spanish version...
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";Y(ESNUMERO(VALOR(EXTRAE(A1;{12456};1)))))
Please note the difference on array notation.
Hi!
Ha ha ha... how did you guess it? Magician?
Actually a much nicer formula, pity that works on D6 instead of A1, but I think I can manage to handle it
Regards!
PS: if somebody wants to know how it becomes on an Spanish version...
=Y(LARGO(A1)=6;EXTRAE(A1;3;1)="/";Y(ESNUMERO(VALOR(EXTRAE(A1;{12456};1)))))
Please note the difference on array notation.
Faseeh
Excel Ninja
Hi All,
It is good to see an interesting and friendly discussion going on, i am not interested in Carlsberg, can you guys give me some non-alcoholic drink??
I was working on something else when i read this post and replied, in future,i will consider using,
for selecting an "appropriate" cell. hehehehe
@Stumpy, Thank you, i think you have abundant supply of A1-based-bug-proof-formulas now!!
Faseeh
It is good to see an interesting and friendly discussion going on, i am not interested in Carlsberg, can you guys give me some non-alcoholic drink??
I was working on something else when i read this post and replied, in future,i will consider using,
Code:
ADDRESS(RANDBETWEEN(1,1048576),RANDBETWEEN(1,16384),4)@Stumpy, Thank you, i think you have abundant supply of A1-based-bug-proof-formulas now!!
Faseeh
Thanks for your efforts guys and the humour too! Tried SirJB7's formula as I cannot speak another language!!!! This threw up a 'may not use unions, intersections or array constants for Data Validation Criteria'. I then took out the unions and there is an error with the formula. i will try to suss it out later, but it may take me the weekend to do that !!!!!!! Thanks once again for all your replies.