8 Replies Latest reply on Jul 1, 2015 5:05 AM by ShaifaliGupta

# Separate Numbers with comma using Indian Format.

### Title

Separate Numbers with comma using Indian Format.

### Post

I'm working in Filemaker Pro 11. I want to display a field's number contents (Amount), rather than as simply a number, as comma separated. Ideally I want to do this while keeping the field contents a number, but displaying it with commas.

For example, the number 100000000 should display (in this layout) as `10,00,00,000`

How can I do this?

• ###### 1. Re: Separate Numbers with comma using Indian Format.

Is it always a 9 digit number? What's the maximum, what's the minimum?  Is it always a whole number?

• ###### 2. Re: Separate Numbers with comma using Indian Format.

You could create a calculation field, result number:

Let([
S = If ( number  < 0 ; "-" ) ;
N = Abs ( number ) ;
C = Ceiling ( Log ( N ) )
];
Case (
not number ; 0 ;
C > 13 ; "number too high" ;
S
&
If ( C ≥ 12 ; Div ( Mod (  N ; 10^13 ) ; 10^11 ) & "," )
&
If ( c ≥ 10 ; Div ( Mod (  N ; 10^11 ) ; 10^9 ) & "," )
&
If ( c ≥ 8 ; Div ( Mod (  N ; 10^9 ) ; 10^7 ) & "," )
&
If ( c ≥ 6 ; Div ( Mod (  N ; 10^7 ) ; 10^5 ) & "," )
&
If ( c ≥ 4 ; Div ( Mod (  N ; 10^5 ) ; 10^3 ) & "," )
&
Mod ( N ; 10^3 )
)
)

This will evaluate numbers negative and positive up to 13 digits.
Note that the field must be formatted by inspector NOT as "General" but "As entered"

• ###### 3. Re: Separate Numbers with comma using Indian Format.

This is another way to do the same thing:

Let([
S = If ( number  < 0 ; "-" ) ;
N = Abs ( Int ( number ) ) ;
L = Abs ( number ) ;
D = Mod ( Abs ( number ) ; 1 ) ;
C = Ceiling ( Log ( L ) )
];
Case(
not number ; 0 ;
C > 13 ; "number too high" ;
S
&
Substitute ( Trim (
Replace ( Replace ( Replace ( Replace ( Replace ( N ; C - 2 ; 0 ; " " ) ; C - 4 ; 0 ; " " ) ; C - 6 ; 0 ;" " ) ; C - 8 ; 0 ; " " ) ; C - 10 ; 0 ; " " )
) ; " " ; "," )
&
If ( D ; D )
)
)

• ###### 4. Re: Separate Numbers with comma using Indian Format.

These twocalculationsfailwhen the numberisexactlya power of 10. ( > 10^2 )
I'm going to revisit them.

• ###### 5. Re: Separate Numbers with comma using Indian Format.

This calculation seems to solve any problem:

Let([
X = YourNumber ;
S = If ( X  < 0 ; "-" ) ;
L = Abs ( X ) ;
N = Abs ( Int ( X ) ) ;
N = Case ( not N ; 0 ; not Mod ( N ; 10^9 ) ; 1 & Left (  "0000000000000" ;  Log ( N ) ) ; N ) ;
D = Mod ( L ; 1 ) ;
C = Ceiling ( Log ( L + 1 ) ) ;
R = Replace ( Replace ( Replace ( Replace ( Replace ( N ; C - 2 ; 0 ; " " ) ; C - 4 ; 0 ; " " ) ; C - 6 ; 0 ;" " ) ; C - 8 ; 0 ; " " ) ; C - 10 ; 0 ; " " )
];
Case(
C > 13 ; "Out Of Range" ;
S & Substitute ( Trim ( R ) ; " " ; "," ) & If ( D ; D )
)
)

• ###### 6. Re: Separate Numbers with comma using Indian Format.

That is pretty amazing...and tedious..Nice job Mr. Raybaudi.  Would be nice to hear from the OP

• ###### 7. Re: Separate Numbers with comma using Indian Format.

See also IndianNotation ( number ) custom function at: http://www.briandunning.com/cf/1789

• ###### 8. Re: Separate Numbers with comma using Indian Format.

Thanks Mr. Raybaudi. It is the best way to solve it. Nice Job...