How to add specific currency symbol?

Hello,

I am trying to automate a smartsheet and I have done everything to this point, and have run into a problem. I have a PDF that needs to have money in it and it could be in any currency. so it could be $10,000 or ¥10,000, etc. But it doesn't fill the PDF with the money symbol and only the amount. It also doesn't add the "," where needed depending on amount which can be between the amount of 1,000-999,999. I know there is probably a formula i can use and i will most likely need helper rows but i am lost on how to create such formula and what is needed. So i need the money symbol with the ability to change to the correct currency and the "," to be placed correctly.

This is how it looks on smartsheet vs how it generates into the PDF.

(¥10,000.00 --> 10000) or (€100,000.00 --> 100000)

The cent isn't nessacary.

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have two options. You can use a nested IF statement to output, or (since it seems like you need quite a few), you can create a table that has the text in one column and the symbol in another column and use an INDEX/MATCH.


    Here is a list of the UNICHAR currency symbols (it may be easier if you are able to type them or copy/paste from somewhere - whichever you prefer):


    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How are you currently populating the sheet with the different currencies?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeremy Y O
    Jeremy Y O ✭✭✭✭

    @Paul Newcome

    Currently I have it as a text field where they can use a form to enter in the amount. But in most cases someone not from the country's currency is filling it out so unless they look up the symbol each time then it wont work. also when trying to do it manually it covert's back to just the number when generating the PDF.

    I haven't had anyone use it yet as I just created the sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion would be a dropdown to select the currency and then a separate field for the amount.


    Then in the sheet you can use a nested IF statement to output the proper currency symbol. Here is an example where I needed either Euros or Dollars:

    IF(Currency@row = "Euro", UNICHAR(8364), "$")


    Then you can use this to convert the number into a text string with commas in place out to 999,999,999,999.

    IF(LEN([Column2]@row) <= 3, RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 6, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 9, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 8, 3), LEFT([Column2]@row, LEN([Column2]@row) - 6)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 12, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 11, 3), LEFT([Column2]@row, LEN([Column2]@row) - 9)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 8, 3), LEFT([Column2]@row, LEN([Column2]@row) - 6)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3))))))


    "Adding" the two formulas together will give you a text string that has the currency symbol as well as the numbers with commas in place. Since it is being stored as a text string instead of a number, it should populate your PDF correctly.


    =currency_IF + comma_formula


    =IF(Currency@row = "Euro", UNICHAR(8364), "$") + IF(LEN([Column2]@row) <= 3, RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 6, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 9, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 8, 3), LEFT([Column2]@row, LEN([Column2]@row) - 6)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3), IF(LEN([Column2]@row) <= 12, IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 11, 3), LEFT([Column2]@row, LEN([Column2]@row) - 9)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 8, 3), LEFT([Column2]@row, LEN([Column2]@row) - 6)), "") + "," + IFERROR(IFERROR(MID([Column2]@row, LEN([Column2]@row) - 5, 3), LEFT([Column2]@row, LEN([Column2]@row) - 3)), "") + "," + RIGHT([Column2]@row, 3))))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeremy Y O
    Jeremy Y O ✭✭✭✭

    @Paul Newcome

    Wow it works great thank you! One more question... how do I make the formula for not just USD and euro but for (Germany, Mexico, United, States, Italy, China, India, Belgium, United, Kingdom, Colombia, Canada, Brazil, Hungary, Lithuania, Thailand, Argentina, Ecuador, Spain, South, Africa, France, Sweden, Australia, Korea, Norway, Netherlands, Ireland, New, Zealand, Finland, Turkey, Japan, Singapore, Switzerland, Denmark, Taiwan) Is there a website I can use I saw you entered UNICHAR(8364) for euro to get the symbol. How would i set the formula to check everything and choose the right one?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You have two options. You can use a nested IF statement to output, or (since it seems like you need quite a few), you can create a table that has the text in one column and the symbol in another column and use an INDEX/MATCH.


    Here is a list of the UNICHAR currency symbols (it may be easier if you are able to type them or copy/paste from somewhere - whichever you prefer):


    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeremy Y O
    Jeremy Y O ✭✭✭✭

    @Paul Newcome

    Thanks a lot helped alot I can take it from here :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!