All geek questions in one place

Parse US number format in German Excel

I am creating an Excel file that helps people automatically process data. On this sheet there are numbers that need to be analyzed (and converted to other units, etc. Simple calculations).

The numbers are in American format, which means they look like this:

0,000,000.00

In Germany, the numbers are displayed like this:

 0.000.000,00

In VBA, I don't really know which localized version of Excel is used (German or English).

Question: Is there an easy way to split U.S. numbers into a Double data type, regardless of the Excel localization used by the user?

+4
source share
2 answers

Some notes. I have not changed the locale to check.

 'http://msdn.microsoft.com/en-us/library/office/bb177675(v=office.12).aspx Debug.Print Application.International(xlCountryCode) 'Excel locale Debug.Print Application.International(xlCountrySetting) 'Windows locale sDecimal = Application.International(xlDecimalSeparator) sThousand = Application.International(xlThousandsSeparator) sNumber = "1,000,000.00" If sThousand <> "," Then If sDecimal <> "." Then sNumber = Replace(sNumber, ",", "") sNumber = Replace(sNumber, ".", sDecimal) End If End If Debug.Print sNumber
+5
source

A possible solution could be:
cells(y, x) = Val(Replace(cells(y, x).Text, ",", ""))

Val always uses. as a decimal mark (for example, CDbl uses cultural settings verified in German Excel).

+1
source

Source: https://habr.com/ru/post/1444758/

More articles:


All Articles