5 Replies Latest reply on Feb 12, 2015 12:33 AM by fayss68

    How to group IBAN Numbers with blanks

    fayss68

      How can I group an IBAN Number separated by blanks for example

       

      DE12341234123412341234

      to achieve

      DE 1234 1234 1234 1234 1234

       

      Thank You

        • 1. Re: How to group IBAN Numbers with blanks
          jlaeby

          Field1 = DE1234abcd5678efgh

           

          Calculation-Field2

           

          Left(Field1;2) & " " & Left(Right(Field1;16);4)&" " & Left(Right(Field1;12);4)&" " & Left(Right(Field1;8);4)&" " & Right ( Field1 ; 4 )

           

          =DE 1234 abcd 5678 efgh

          • 2. Re: How to group IBAN Numbers with blanks
            erolst

            jlaeby wrote:

             

            … Left(Right(Field1;16);4) …

            Could I interest you in Substitute(), List() and Middle() and whitespace?

             

            Substitute (

              List (

                Left ( IBAN ; 2 ) ;

                Middle ( IBAN ; 3 ; 4 ) ;

                Middle ( IBAN ; 7 ; 4 ) ;

                Middle ( IBAN ; 11 ; 4 ) ;

                Middle ( IBAN ; 15 ; 4 )

              ) ; ¶ ; " "

            )

            • 3. Re: How to group IBAN Numbers with blanks
              JavierDura

              Don´t forget two things:

              - remove unwanted spaces using the Trim () function.

              - validate that you have the right number of characters using the Length () function.

              • 4. Re: How to group IBAN Numbers with blanks
                keywords

                As shown by erolst, use a Let ( ) calculation. Here is a calc I built for a similar purpose. I use the calc as an autoenter that replaces whatever contents the user enters into the field:

                 

                // format <DATA ENTERED IN A PARTICULAR FIELD> in the standard format required

                // NOTE:

                // 1.  the above calculation applies standard formatting to any <DATA AS ENTERED>

                // 2. result (1) must contain numerals only, (2) there must be 11 numerals, and (3) data must be in the form xxx xxx xxx xxx

                ––––––––––––

                Let ( [

                rawData = Self      //this is whatever the user enters in the field

                ; filtered = Filter ( rawData ; "0123456789¶ " )     //remove only letters that have been entered, for use below

                ; numerals = Substitute ( TrimAll ( filtered ; 0 ; 3 ) ; "¶" ; "" )     //remove any carriage returns and spaces that have been entered

                ; length = Length ( numerals )

                ; letters = filtered ≠ rawData

                ; long = length > 11

                ; short = length < 11

                ; correct = length = 11

                ; result =

                    Case (

                            letters ; "INCORRECT: " & rawData & " —must contain numerals only" ;

                            long ; "INCORRECT: " & numerals & " —too many digits" ;

                            short ; "INCORRECT: " & numerals & " —insufficient digits" ;

                            correct ; Middle ( numerals ; 1 ; 2 ) & " " & Middle ( numerals ; 3 ; 3 ) & " " & Middle ( numerals ; 6 ; 3 ) & " " & Middle ( numerals ; 9 ; 3 ) ;

                               )

                ] ;

                result

                )

                • 5. Re: How to group IBAN Numbers with blanks
                  fayss68

                  Thank you very Much!

                   

                  Kind regards