Currency Format issue with imported data
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
-
"." is the separator between dollars and cents.I dont understand what the issue is?
-
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.
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,
GenevieveJoin us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives