Formatting Prices

[Emma in England]

I can't believe this hasn't been covered before, but my searches yielded no results so here goes.

I import prices from an excel file, into a number field. On the layout it is formatted to show with currency and 2 decimal places, e.g. £5.00

Is there an easy calculation that will turn '5' into '£5.00'?

At present I have a cumbersome one based on case and the position of '.' in the field, but I'd love it to be simpler. I can't use custom functions, but wondered if there was something cunning I could do with the standard functions that had escaped me?

(The reason for this is that the main function of the database is to export tagged text for Quark. The export field is a long and complex calculation, so I can't just export the price field with formatting on its own.)

[LaRetta]

If you can type a '£' on your keyboard then you should be able to do it. Handle it from Format > Number. Specify decimal and 2 places. Right below is Use Notation. Type the '£' within the currency symbol box.

[Emma in England]

Yes, I've got that. But I need the actual content of the field to be '£5.00' whereas it is now '5'. At present I am defining a calculation field called 'PriceAsText' which contains

"£" & Case ( Position ( Price ; "." ; 1 ; 1 ) =0; Price & ".00"; Position ( Price ; "." ; 1 ; 1 ) =Length(Price)-2 ; Price;Position (Price ; "." ; 1 ; 1 ) =Length(Price)-1 ;Price & "0";"")

- which seems long and clunky to me. I was hoping someone had a quick trick!

[comment]

Try:

"£" &

Int ( Price )

& "." &

Right ( "00" & Price * 100 ; 2 )

This assumes Price is not negative, and that it has been pre-rounded to 2 decimal places.

For a more flexible formula see:

http://fmforums.com/forum/showpost.php?post/152886/

[Emma in England]

Cheers, Comment, that's shorter! And yes, I can assume both of those things.