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



"Rainer Bielefeld" skrev i meddelelsen
...
why use Val then?

"Per Jessen" schrieb im Newsbeitrag
...
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" skrev i meddelelsen
...
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" schrieb im Newsbeitrag
...
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" skrev i meddelelsen
...
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



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