String to Number Conversion Problem

[Rainer Bielefeld]

Hi,

I'm running a german Office, which means Decimal-Seperator is "," and Thousands-Seperator is "."
and I want to convert Strings which are using "." and "," as Decimal- and Thousands-Separator to Numbers.

For example I want to convert "25.000" to 25.

I've tried

Application.UseSystemSeparators = False
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","

vDouble = CDbl("25.000")

Application.DecimalSeparator = ","
Application.ThousandsSeparator = "."
Application.UseSystemSeparators = True

but the result is 25000?

Can anyone help?

Regards,

Rainer

 

[Per Jessen]

Hi Rainer

You can use a simple substitute function:

a = WorksheetFunction.Substitute("25.000", ".", ",", 1)
vDouble = CDbl(a)

Regards,
Per

 

[Rainer Bielefeld]

Hi Per,

thanks for your help.

You can use a simple substitute function:

a = WorksheetFunction.Substitute("25.000", ".", ",", 1)
vDouble = CDbl(a)

sure - but this is somehow stupid, isn't it?
And I don't like it - I think, it's not good practice.

Regards,

Rainer

 

[Per Jessen]

Rainer,

I guess you are right, so I found that you have to convert your text string
into a true value, then as excel always use '.' as decimal delemiter, this
single line is what you need:

vDouble = CDbl(Val("25.000"))

Best regards,
Per

 

[Rainer Bielefeld]

Per,

it's not a good idea to use Val, if Thousands-Separator is used as well.

eg. cdbl(val("10,025.000")) will not bring the correct result ;-)

Regards,

Rainer

 

[Per Jessen]

Rainer,

Then we substiture Thousands-Separator with nothing and use Val (will also
work if no Thousands-Separator is found):

vDouble = CDbl(Val(WorksheetFunction.Substitute("10,025.000", ",", "", 1)))

Regards,
Per

 

[Rainer Bielefeld]

why use Val then?

Rainer,

Then we substiture Thousands-Separator with nothing and use Val (will also
work if no Thousands-Separator is found):

vDouble = CDbl(Val(WorksheetFunction.Substitute("10,025.000", ",", "", 1)))

Regards,
Per

 

[John_John]

Hi Rainer!

Use the expression below to convert the string to a correct european number:

vDouble = cdbl(Replace(Replace("25.000",",",vbNullString),".",","))

Ο χÏήστης "Rainer Bielefeld" έγγÏαψε:

 

[Per Jessen]

Because Substitiute is in this case only used to remove Thousands-Separator,
so without Val the result will be 10025000