Inserting a Decimal, Comma, & Dollar Signs

[Mari McGrath]

I am trying to insert a decimal to a whole number.  The customer data is "267262" and I need to have it format as "$2,672.62". 

Formally I would use FormatNumber(|->NumberField, "$###,###,###.##"), but it will format my number as "$267,262". 

Not all the numbers in this field are the same length either.  How can I make this work?

Thanks everyone!

Mari

[Paul Abney]

This is how I would begin to do it:

FormatNumber(SubString(|->NumberField, 0, (Length(|->NumberField) - 2)) & "." & SubString(|->NumberField, (Length(|->NumberField) - 2), 2), "$###,###,###.##")

[couch]

maybe dividing by 100 and formatting the result?

[Paul Abney]

I officially bow to you couch, I have been schooled this is a much better solution.

[couch]

No need to bow - it simply shows that I have already been "schooled" so many times by my peers and colleagues that finally start to remember :)

[Mark Kuehn]

Use FormatNumber(|->NumberField/100, "$###,###,###.##”)

-Mark

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xmpie-users...@googlegroups.com.
To post to this group, send email to xmpie...@googlegroups.com.
Visit this group at https://groups.google.com/group/xmpie-users.
For more options, visit https://groups.google.com/d/optout.

[Mari McGrath]

Dividing by 100 in the FormatNumber works great for most of my records! 

I have a follow up question though; how would you go about getting the 0 back on the number if is it "157590", formats as "$1,575.9", but should be "$1,575.90"?

[George Marsh]

The # mark is a placeholder. 

If you want 2 dp to always show you can format as #,##0.00 (with enough # for your biggest expected number.)

Note unlike Excel formatting this doesn't round the figure, just truncates, so if you need to apply a round() as well.