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

 

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:

1. Conditional formatting is not validation.
2. You could make it easier for your users by letting them enter the digits only, and format the result yourself.

Edited October 1, 20196 yr by comment

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:

1. Conditional formatting is not validation.
2. 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

1 minute ago, lutfishah said:

Please see my code below

Does it work the way you want?

 

Sorry, it didn’t work

I am afraid I no longer follow what's going on here.

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 )
   )

 

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