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

# How to group IBAN Numbers with blanks

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

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

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

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

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

Thank you very Much!

Kind regards