lutfishah Posted October 1, 2019 Posted October 1, 2019 Dear All, I am trying to put a conditional formatting validation which turns Red when the value is entered wrong. Basically all my tries have failed so I would like your help here. The criteria of entering a value is xxxx-xxxx-xxxx-xxxx 1111-2222-3333-4444 1. Length of value is = 19 OR this should be a 16 digit number, in groups of 4 separated with dashes 2. Non-Digits characters are not valid. Please could you help me with this and I will really appreciate. Many thanks Regards Lutfi
comment Posted October 1, 2019 Posted October 1, 2019 (edited) Try this as the conditional formatting formula: Substitute ( Yourfield ; [ "1" ; "0" ] ; [ "2" ; "0" ] ; [ "3" ; "0" ] ; [ "4" ; "0" ] ; [ "5" ; "0" ] ; [ "6" ; "0" ] ; [ "7" ; "0" ] ; [ "8" ; "0" ] ; [ "9" ; "0" ] ) ≠ "0000-0000-0000-0000" Note: Conditional formatting is not validation. You could make it easier for your users by letting them enter the digits only, and format the result yourself. Edited October 1, 2019 by comment
lutfishah Posted October 1, 2019 Author Posted October 1, 2019 Thanks for your reply 7 minutes ago, comment said: Try this as the conditional formatting formula: Substitute ( Yourfield ; [ "1" ; "0" ] ; [ "2" ; "0" ] ; [ "3" ; "0" ] ; [ "4" ; "0" ] ; [ "5" ; "0" ] ; [ "6" ; "0" ] ; [ "7" ; "0" ] ; [ "8" ; "0" ] ; [ "9" ; "0" ] ) = "0000-0000-0000-0000" Note: Conditional formatting is not validation. You could make it easier for your users by letting them enter the digits only, and format the result yourself. Correct that Conditional formatting is not a validation but we just are using it for visual validation that if a value is incorrect then it should only highlight it by filling a red colour. Please see my code below. Case ( Filter (Left(myfield ; & Middle(myfield ;6; 4) & "-" & Middle(myfield ;11; 4) & "-" & Right(myfield ; 4); "abcdefghijklmnopqrstuvwxyz_@!#$%&'*+/=?^_`{|}~") ; 0 ; 1) = 0 or If (Length ( myfield ) = 19 ; 1 ; 0 ) = 0 and IsEmpty (myfield ) ≠ 1
comment Posted October 1, 2019 Posted October 1, 2019 1 minute ago, lutfishah said: Please see my code below Does it work the way you want?
comment Posted October 1, 2019 Posted October 1, 2019 I am afraid I no longer follow what's going on here.
OlgerDiekstra Posted October 1, 2019 Posted October 1, 2019 How about: Let([ ~field = Filter( myField; "0123456789" ) ]; If( Length( ~field ) = 16 and Left( ~field; 4 ) & "-" & Middle( ~field; 6;4 ) & "-" & Middle( ~field; 11; 4 ) & "-" & Right( ~field; 4 ) = myField; 1; 0 ) ) 1
lutfishah Posted October 1, 2019 Author Posted October 1, 2019 14 minutes ago, OlgerDiekstra said: How about: Let([ ~field = Filter( myField; "0123456789" ) ]; If( Length( ~field ) = 16 and Left( ~field; 4 ) & "-" & Middle( ~field; 6;4 ) & "-" & Middle( ~field; 11; 4 ) & "-" & Right( ~field; 4 ) = myField; 1; 0 ) ) Perfect @OlgerDiekstra you did it It works. Great thanks And thanks to @comment as well
Recommended Posts
This topic is 2149 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now