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! :smiley:

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"
  )
)

Screenshot 2022-06-19 at 2.00.46 PM

1 Like

Thank you so much! That worked! :smiley:

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.

Screenshot 2022-06-20 at 2.43.02 PM

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.