Currency Format issue with imported data

Karim Bizid

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

BullandKhmer

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

Genevieve P.

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