String to Number Conversion Problem

  • Thread starter Thread starter Rainer Bielefeld
  • Start date Start date
R

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
 
Hi Rainer

You can use a simple substitute function:

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

Regards,
Per
 
Hi Per,

thanks for your help.
You can use a simple substitute function:

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

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

Regards,

Rainer
 
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
 
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
 
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
 
why use Val then?

Per Jessen said:
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
Click to expand...
 
Hi Rainer!

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

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

Ο χÏήστης "Rainer Bielefeld" έγγÏαψε:
 
Because Substitiute is in this case only used to remove Thousands-Separator,
so without Val the result will be 10025000
 
Back
Top