
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 18, 2022 10:59 AM
I am trying to format decimals in a formula to no avail:
- To the left is the number
- the 3rd column is what happens when this number is used in a formula (the end is cut off for numbers ending in 0) (e.g. 12.50 > £12.5
- middle column is where I have formatted the number with this formula:
so this was a solution found on another thread:
IF({Refill / lowest price}, “£” & {Refill / lowest price} & IF(FIND(".", {Refill / lowest price} & “”) = LEN({Refill / lowest price} & “”) - 1, “0”))
However, if you now look at the middle column it works for the £12.50, but for any round numbers under 10 it misses the decimal place. (i.e. 5.00 > £50.
Any help with tweaking this formula would be greatly appreciated!
THANKS! :grinning_face_with_big_eyes:
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 18, 2022 11:01 PM
Hi Cassy, could you try the following formula and see if it works for you?
IF(
Amount,
IF(
FIND(".", Amount & ""),
IF(
LEN(Amount & "") - FIND(".", Amount & "") = 1,
Amount & "0",
Amount
),
Amount & ".00"
)
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 18, 2022 11:01 PM
Hi Cassy, could you try the following formula and see if it works for you?
IF(
Amount,
IF(
FIND(".", Amount & ""),
IF(
LEN(Amount & "") - FIND(".", Amount & "") = 1,
Amount & "0",
Amount
),
Amount & ".00"
)
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 19, 2022 05:06 AM
Thank you so much! That worked! :grinning_face_with_big_eyes:
I have used the same formula in another area, and wondered if you could help with this too please?
- middle is formatted number using:
ROUND({PRICE PER}, 2)
But once in a another formula it cuts off the 0
-
- The far right column is using your formula above
Thanks again!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 19, 2022 11:44 PM
Hi Cassy, hmm, could you share a link to your base with me so I can try to troubleshoot it?
I’ve tried to replicate your setup here but it seems to be working fine for me.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 20, 2022 03:43 AM
Adam, you’re a star, it works! I was choosing to either use the ROUND function OR your formula, but I realised it requires both:
- Left column is amount formatted with:
ROUND({PRICE PER}, 2)
- Right column is your formula applied using the above already rounded number:
IF(
Amount,
IF(
FIND(".", Amount & “”),
IF(
LEN(Amount & “”) - FIND(".", Amount & “”) = 1,
Amount & “0”,
Amount
),
Amount & “.00”
)
)
- Middle column is now pulling the finalised “double formatted” number.
Thanks so much for all your help!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 20, 2022 04:45 AM
No worries, glad I could help!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 13, 2023 12:16 PM
It is absolutely insane that this is the solution to getting formula results to display decimal places
