AnsweredAssumed Answered

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)

 

)

Outcomes