CODEWITHSUNDEEP
vbaexcellocalizationnumbers
Benedikt
Benedikt

Reputation: 974

Parse US number format in German Excel

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

The numbers come in US format which means they look like:

0,000,000.00

In Germany, numbers are displayed like this:

0.000.000,00

In VBA, I actually don't know, which localized Version of Excel is in use (German or English).

Question: Is there an easy way to parse the US format numbers into data type Double regardless of the Excel localization, that is used by the user?

Upvotes: 4

Views: 4143

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

Some notes. I did not change locale to test.

'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

Upvotes: 5

KekuSemau
KekuSemau

Reputation: 6853

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

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

Upvotes: 1

Related Questions