String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

Hi Rainer

You can use a simple substitute function:

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

Regards,
Per

"Rainer Bielefeld" skrev i meddelelsen
...
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

String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

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

String to Number Conversion Problem
 

Hi Rainer!

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

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

Ο χÏ�ήστης "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

.

String to Number Conversion Problem
 

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