For readability it would be nice to be able to present numbers (especially large ones) with separators such as commas. Custom defined number formats would be even better (regex).
Thanks Josh, for additional thousand separators this workarround is fine. Still not working for folks, who wants to have something like this: 12.549,12 instead of 12,549.12
Thank you, Johannes! This was extremely close to what I was looking for. I wanted to improve the appearance of the Market Cap and 24hr Volume in my Crypto Portfolio. I use the CoinMarketCap API to import current values into my table, but the numbers are so huge it is almost impossible to read them. I modified your formula a bit to fit my situation and it worked beautifully. I will leave a reference of my work for the Market Cap field here in case anyone else is looking for a way to abbreviate long financial based numbers.
As you can see, the {USD.market_cap} field is a very long number set to the maximum decimal places. I used three separate formula fields to come to my final Market Cap field. Step 1
Create a new formula type field. Mine is titled “mc_1”. Use a formula to reference back to your large number field to remove all places after the decimal. My large number field is titled “USD.market_cap”.
ROUND({USD.market_cap},0)
Step 2
Create a second formula type field. Mine is titled “mc_2”. This time your formula will create a string from your number in your first reference field.
CONCATENATE(mc_1)
Step 3
Create one last formula field. Mine is titled “Market Cap”. This will be the one that tidies up the long string of numbers. It will reference both of the previous fields you just made. Be sure to reference the field from Step 2 for the (LEN) portion of your formula, and to reference the field from Step 1 for the mathematical portion of your formula.
Seriously – really just needed to do a good old fashioned left-padded ID number, being able to format with a specification of “00000” would have made it work a lot, lot cleaner.
