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







DEUX=    If (

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





TROIS=    If (

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







QUATRE=    If (

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





CINQ=    If (

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







SIX=    If (

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




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




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