Formatting numbers for the clipboard in FileMaker Pro

Formatting numbers for the clipboard in FileMaker Pro

Preliminary remark: The formatting below uses "." as a thousands separator, "," as a decimal comma and ";" as a formula separator, instead of the "," and the "." and the "," respectively you might be using, especially in English systems. Please change accordingly if necessary. Moreover, the 2 decimals calculated below can be changed as well of course.

Copying numbers, formatted with 2 decimals and thousands separators, looking like this:

0,11
0,01
0,10
123.456.789.012.345,67

to the clipboard and then pasting them into a text field or into another (text) application, will result in:

0,11
0,01
0,1
123456789012345,67

The latter two lines are not nice. The following calculation solves these problems and also rounds to 2 decimals in case a calculated number results internally more than 2 decimals. It also deals with negative numbers correctly. By the way, DO NOT use the truncate function in FMP, it's buggy! See my workaround in c.fmp.truncate.bug.html.

n.number is the field you want to send in a correct "text" format to the clipboard.

For this to achieve, create

c.number.formatted.as.text.with2decimals (result = text), unstored

with the following formula:

Case(
IsEmpty(n.number);"";

Case(

Int(n.number)=0; "0";

Abs(n.number)>999999999999999,99;
"Clipboard maximum = 999.999.999.999.999,99";

Length(Int(n.number))=15;
Left(Int(n.number);3)
& "." &
Middle(Int(n.number);4;3)
& "." &
Middle(Int(n.number);7;3)
& "." &
Middle(Int(n.number);10;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=14;
Left(Int(n.number);2)
& "." &
Middle(Int(n.number);3;3)
& "." &
Middle(Int(n.number);6;3)
& "." &
Middle(Int(n.number);9;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=13;
Left(Int(n.number);1)
& "." &
Middle(Int(n.number);2;3)
& "." &
Middle(Int(n.number);5;3)
& "." &
Middle(Int(n.number);8;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=12;
Left(Int(n.number);3)
& "." &
Middle(Int(n.number);4;3)
& "." &
Middle(Int(n.number);7;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=11;
Left(Int(n.number);2)
& "." &
Middle(Int(n.number);3;3)
& "." &
Middle(Int(n.number);6;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=10;
Left(Int(n.number);1)
& "." &
Middle(Int(n.number);2;3)
& "." &
Middle(Int(n.number);5;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=9;
Left(Int(n.number);3)
& "." &
Middle(Int(n.number);4;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=8;
Left(Int(n.number);2)
& "." &
Middle(Int(n.number);3;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=7;
Left(Int(n.number);1)
& "." &
Middle(Int(n.number);2;3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=6;
Left((n.number);3)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=5;
Left(Int(n.number);2)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))=4;
Left(Int(n.number);1)
& "." &
Right(Int(n.number);3);

Length(Int(n.number))<=3;
Int(n.number)
)

&
Case(
Abs(Round((n.number - Int(n.number))*100;2)) <>0
and
Abs(Round((n.number - Int(n.number))*100;2)) <10;
",0" & Abs(Round((n.number - Int(n.number))*100;2));

Abs(Round((n.number - Int(n.number))*100;2)) <>0
and
Abs(Round((n.number - Int(n.number))*100;2)) >=10;
"," & Abs(Round((n.number - Int(n.number))*100;2));

"," & "00"

)
)

Good luck!

Back to the list of my FileMaker Pro trouvailles