# HELP! Formatting Decimals in Formulas

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! 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"
)
)
`````` 1 Like

Thank you so much! That worked! 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

1. The far right column is using your formula above

Thanks again!

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. 1 Like

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!

1 Like

No worries, glad I could help!

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.