Hi, I have a list of values that I am trying to format as Currency. I had to change the way the raw data is so that it would reflect my local currency format (XXXX,XX), but when I try to format the cell to currency, it won't work, as if the formula result is regarded as text or something along these lines.
Any ideas?
[Edited by Moderator]
MacBook Pro 16″, macOS 15.4
Thanks! I changed the formatting for numbers in System Preferences and now it works a charm! :)
Ordinarily, the 'ocurrence' and 'second-occurrence' labels in the TEXTBEFORE() and TEXTBETWEEN() are placeholders that need to be replaced with values indicating which fields you want.
To get the text before the first comma the correct use of TEXTBEFORE() would be:
TEXTBEFORE( $F2, "," 1)
You're lucky in that Numbers defaults to 1 if occurrence isn't set, but it's worth noting for future reference.
In either case, I'd take a slightly shorter approach, namely:
=INT(F2)&","&(F2−TRUNC(F2))×100
The idea here is that INT(F2) grabs the integer part of the cell (i.e. dollars) and the (F2-TRUNC(F2)) returns the decimal (or cents). These are then concatenated with a comma to get the visual format you want.
HOWEVER, both approaches will return a TEXT object, because that is what it is... it's a combination of text elements. Numbers will not recognize this as a number (or a currency) because it does not match the localized format.
That said, if your system settings are set such that you use comma for decimal values, then this shouldn't be an issue (although then both column F and H should show comma-formatted currencies. You may have to pick your battles ;)
Cannot format Formula result as Currency
