String to Number Conversion Problem - microsoft.public.excel.programming

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

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

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

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

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

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

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

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

Delete Rows that do not contain

189

Utf

Delete Table row with first cell not empty

112

GBExcel

Passing Information to and from Forms

dtshedd

Color Codes: Different color with same color Index

8405

Michiel

Moving cells to row with matching data

113

gcotterl

Email from Excel Function

111

Utf

Another '03 vs '07 Add-in Menu Question

143

mrkt_rwnd

append all instead of one

Utf

Text in formula

Utf

Copy/Paste Values code

Utf

auto filtering on excel

109

Hagar

Range error

108

Utf

Updated Calendar Tool (No Macros)

147

Alex

Code to copy worksheet

130

Utf

Sheet and workbook code: All sheets should have row 1 to 5 of Sheet1 to be repeated at top

104

Subodh

8/7/2012 12:59:22 AM