CurrencyFormatForText ( ValueText )

Translate numbers stored as text into US Dollars (currency)

Average rating: 4.6 (29 votes) Log in to vote

John Shields
Chester County Intermediate Unit
http://www.cciu.org

Sample input:

CurrencyFormatForText ( 23.1 )

Sample output:

$23.10

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ( 
[ 

@Value = Case ( ValueText = "" or ValueText = 0 ; "0.00" ; ValueText ) ; 
@Value = Round ( @Value ; 2 ) ; 
@Value = Case ( not PatternCount ( @Value ; "." ) ; @Value & ".00" ; @Value ) ; 

@Length = Length ( @Value ) ; 
@Pos = Position ( @Value ; "." ; 1 ; 1 ) ; 

@Left = Left ( @Value ; @Pos -1 ) ; 

@Right = Right ( @Value ; @Length - @Pos ) ; 
@Right = Left ( @Right & "000" ; 2 ) 
] ; 

"$" & @Left & "." & @Right 

)

When you need to display a number as text, you will lose the ability to format for currency. This will let you maintain the formatting.

If referencing a whole number, ".00" will be added. If only 1 decimal, "0" will be added to the end. Also, the dollar sign will be added by default. (Can easily be modified to accommodate any currency symbol.)

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

CurrencyFormatForText ( ValueText )

Comments

Support this website.

Support this website.