Calculation for Postal Code format

Question asked by Cécile on Apr 28, 2018
Latest reply on Apr 28, 2018 by Cécile

I wrote a calculation to format postal codes properly regardless of how they were entered

J6Y2A6   j6y2a6  J6Y 2A6  j6y 2a6 J6Y-2A6  j6y-2a6   <- all these would format to J6Y 2A6

H2K3KO or JlM iB4<- would return nothing because the word Erreur replaces the wrong data provided

So if I type J6Y2A6 in the field, I should get J6Y 2A6 upon exiting. However, here is the result i get if I put CODE for false as well as for true to see what is happening:

ERREUR6ERREUR 2ERREUR6   <- testing the letters (variables UN  TROIS  CINQ) gives a false negative, I don't know why.

Let( [

Postal = Filter(Postal Code;"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");

UN=    If (

Filter (Left(Postal;1); "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");

Left(Postal;1);

"Erreur"

);

DEUX=    If (

Filter (Middle(Postal;2;1); "0123456789");

Middle(Postal;2;1);

"Erreur"

);

TROIS=    If (

Filter (Middle(Postal;3;1); "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");

Middle(Postal;3;1);

"Erreur"

);

QUATRE=    If (

Filter (Middle(Postal;4;1); "0123456789");

Middle(Postal;4;1);

"Erreur"

);

CINQ=    If (

Filter (Middle(Postal;5;1); "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");

Middle(Postal;5;1);

"Erreur"

);

SIX=    If (

Filter (Middle(Postal;6;1); "0123456789");

Middle(Postal;6;1);

"Erreur"

);

CODE= Upper (UN & DEUX & TROIS & " " & QUATRE & CINQ & SIX)

];

Case (PatternCount ( CODE ; "Erreur" )>0;""; CODE)

)