Currency Format issue with imported data

Options

Hi all!

I am working with imported data from an API source over which I have absolutely no control.
The financial data (euro's in this case) is coming into the sheet with a dot as a cents separator.
Example:

1000.50 = a thousand euro's and 50 cents.

Because of the dot Smartsheet wont accept formatting this column as a currency.

What I have already tried to do is chancing the dot into a comma in a new column alongside it with the folowing formula:
=SUBSTITUTE([Name of Column]@row, ".", ",")

Visually this changes it to a comma, but it still won't enable me to format the new colum into a currency format.

Changing the dot to a comma in the orignal column by hand works instantly but onfurtnatly I need something automated since it's a daily import.

Is there anyone who can point me into the right direction?

Thanks in advance!

Karim

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    "." is the separator between dollars and cents.I dont understand what the issue is?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Since they're talking about Euros (not dollars), it sounds like they're using a Regional Preference in their personal settings that has commas as a decimal separator:

    @BullandKhmer If you're using dollars and cents, then you could reverse the question to see the same issue:

    • Cell contains: 200,50
    • Use the formula: =SUBSTITUTE([Name of Column]@row, ",", ".")
      This does not change the numbers into a value.

    @Karim Bizid

    You can use the VALUE function around that same formula to turn it into a number:

    =VALUE(SUBSTITUTE([Name of Column]@row, ".", ","))

    Then just make sure you're increasing what decimals are showing in that column:

    Cheers,
    Genevieve