CODEWITHSUNDEEP
excelvba
Chri.s
Chri.s

Reputation: 1456

Decimal number in string-data-type with large amount of decimals always interpreted as large integer (regional decimal separator issue)

Background: I'm receiving data for my Excel application from an API in JSON format. For this matter I'm receiving numerical values as a string, as everything sent in JSON naturally is a text format - and so does VBA also interpret it. As I'm located in Denmark, using a different decimal separator than the native on in Excel (my Danish version utilizes , as separator rather than .).

Case: This is causing quite a bit of trouble as Excel interprets this as a thousand-separator when converting the string to a number.

Searching for answers I've found that the best solution, normally, is to convert the string to double when using VBA, utilizing CDbl(string to convert to number).

This usually is the case, but in my case I'm receiving a number with a lot of decimals such as: "9.300000190734863".

When doing a CDbl("9.300000190734863") this results in a very large integer: 9,30000019073486E+15

Also, I don't think utilizing a replace() approach is feasible in my case as I might also have data that uses both decimal- and thousand separators at the same time, making my results prone to replacement errors.

However, inserting the string value directly into a cell within Excel converts the number correctly to 9,30000019073486 in my case.

Question: Can it be right that there's no way to mimic, or tap into, this functionality that Excel obviously is using when inserting the string into a cell?

I've searched for quite some time now, and I haven't found any solution other than the obvious: inserting the value into a cell. The problem here is that it's giving me some performance overhead which I would rather avoid.

Upvotes: 1

Views: 339

Answers (1)

Josh Eller
Josh Eller

Reputation: 2065

You can swap the positions of the periods and commas in your input prior to casting as a double, in three steps:

  1. Replace commas with 'X' (or some other value that won't appear in your data)
  2. Replace periods with commas
  3. Replace 'X' with periods

Upvotes: 1

Related Questions