Formular field to show decimals while concatenated with text

[Kim_Trager1]

I’m trying to make a basic set up for switching between currencies when pricing a product
I’ve got 2 fields; one for € and one for £ and then a formula field that’ll show the price from the £ field or the € field depending if a ‘switch’ is set to £ or €.
I really want to show the displayed price with 2 decimals, but it doesn’t show a price is fx. 2.00.
I tried with the ROUND() function but doesn’t work - is there a way for 2 decimals in the formula field.

Currently, I’m using the following:

IF({Currency switch}=“€”, "€ " & Euro, IF({Currency switch}=“£”, "£ " & ROUND(pounds,2), BLANK()))

[bdelanghe]

This is a little weird. When you use & it converts the currency into a string. So you can’t just format it you have to come up with some string logic:

IF({Currency switch}='€','€ ' & Euro & IF(LEN(Euro&'')=1,'.00',IF(FIND('.',RIGHT(Euro&'',2)),'0')), IF({Currency switch}='£','£ ' & pounds & IF(LEN(pounds&'')=1,'.00',IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

You’ll first notice I am joining both pounds and euros with an empty string constantly. That’s so it gets converted before I use LEN, FIND and RIGHT. After that, I check to see if the total length is 1 (ie: £ 2) if it is I add ‘.00’. Otherwise, I check the right to digits to see if there is a decimal if there is I add a zero.

[edit: small fix in the equation]

[W_Vann_Hall]

@Kim_Trager1, you may (or may not, depending on how deeply down the rabbit hole you wish to tumble) want to look at my number and currency ‘pretty-print’ routines for code supporting appropriate right-aligned, fixed-decimal currency values.

[Kim_Trager1]

@bdelanghe, Thank you for your explanation. I’m playing around with your suggestions - and trying to wrap my head around how it works. However, it seems that the script no longer adds decimals when the string has more than 2 digits - any suggestions on how to counter this?

@W_Vann_Hall thank you for giving access to your base. The number table seems to be what I need. I need to study it more as the rabbit hole looks very deep.

Merry Christmas to both of you

[bdelanghe]

Thanks for the Christmas wishes! After a little break it took me a little while as well to understand my formula. (And I found the mistake I made) I’ve added tabs which should help a little:

IF({Currency Switch}='€', //check the switch
    '€ ' & Euro & //start joining the string for Euro
  
    IF(NOT(FIND('.',Euro&'')),'.00', //if you can't find a decimal add it and '00'
    IF(FIND('.',RIGHT(Euro&'',2)),'0')), //otherwise check if the decimal is in the last two add a zero
    //but if everything is okay we are just joining the string to nothing 

IF({Currency Switch}='£',  //same as above, but pounds this time
    '£ ' & pounds &

    IF(NOT(FIND('.',pounds&'')),'.00',
    IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

That helps to show that it’s just the same thing twice, but here’s a screen of a table that breaks out a few of the sub-steps:

Also just incase you want to copy paste here is the formula without comments:

IF({Currency Switch}='€',
  '€ ' & Euro &
  
  IF(NOT(FIND('.',Euro&'')),'.00',
  IF(FIND('.',RIGHT(Euro&'',2)),'0')),

IF({Currency Switch}='£',
  '£ ' & pounds &

  IF(NOT(FIND('.',pounds&'')),'.00',
  IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

Hope this helps. Also don’t forget the string joins inside each formula is important because it converts the number to a string ( ie: pounds&’’ )