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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.