MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### String to Number Conversion Problem

• Follow

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

 0

Hi Rainer

You can use a simple substitute function:

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

Regards,
Per

"Rainer Bielefeld" <newsspam@t-online.de> skrev i meddelelsen
news:heapn8\$j3k\$03\$1@news.t-online.com...
> 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
>

 0

Hi Per,

> 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
>>
>
 0

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" <newsspam@t-online.de> skrev i meddelelsen
news:heb1ta\$f69\$00\$1@news.t-online.com...
> Hi Per,
>
>
>> 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
>>>
>>

 0

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" <per.jessen@mail.dk> schrieb im Newsbeitrag news:uC58s91aKHA.1640@TK2MSFTNGP06.phx.gbl...
> 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" <newsspam@t-online.de> skrev i meddelelsen
> news:heb1ta\$f69\$00\$1@news.t-online.com...
>> Hi Per,
>>
>>
>>> 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
>>>>
>>>
>
 0

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" <newsspam@t-online.de> skrev i meddelelsen
news:heb6oe\$bom\$03\$1@news.t-online.com...
> 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" <per.jessen@mail.dk> schrieb im Newsbeitrag
> news:uC58s91aKHA.1640@TK2MSFTNGP06.phx.gbl...
>> 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" <newsspam@t-online.de> skrev i meddelelsen
>> news:heb1ta\$f69\$00\$1@news.t-online.com...
>>> Hi Per,
>>>
>>>
>>>> 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
>>>>>
>>>>
>>

 0

why use Val then?

"Per Jessen" <per.jessen@mail.dk> schrieb im Newsbeitrag news:e0WZ0i2aKHA.808@TK2MSFTNGP02.phx.gbl...
> 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" <newsspam@t-online.de> skrev i meddelelsen
> news:heb6oe\$bom\$03\$1@news.t-online.com...
>> 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" <per.jessen@mail.dk> schrieb im Newsbeitrag
>> news:uC58s91aKHA.1640@TK2MSFTNGP06.phx.gbl...
>>> 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" <newsspam@t-online.de> skrev i meddelelsen
>>> news:heb1ta\$f69\$00\$1@news.t-online.com...
>>>> Hi Per,
>>>>
>>>>
>>>>> 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
>>>>>>
>>>>>
>>>
>
 0

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
>
> .
>
 0

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

"Rainer Bielefeld" <newsspam@t-online.de> skrev i meddelelsen
news:hebc16\$829\$02\$1@news.t-online.com...
> why use Val then?
>
> "Per Jessen" <per.jessen@mail.dk> schrieb im Newsbeitrag
> news:e0WZ0i2aKHA.808@TK2MSFTNGP02.phx.gbl...
>> 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" <newsspam@t-online.de> skrev i meddelelsen
>> news:heb6oe\$bom\$03\$1@news.t-online.com...
>>> 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" <per.jessen@mail.dk> schrieb im Newsbeitrag
>>> news:uC58s91aKHA.1640@TK2MSFTNGP06.phx.gbl...
>>>> 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" <newsspam@t-online.de> skrev i meddelelsen
>>>> news:heb1ta\$f69\$00\$1@news.t-online.com...
>>>>> Hi Per,
>>>>>
>>>>>
>>>>>> 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
>>>>>>>
>>>>>>
>>>>
>>

 0