| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| String formula conversion | Excel Discussion (Misc queries) | |||
| string conversion problem | Excel Programming | |||
| Simple String Conversion - HELP | Excel Programming | |||
| Range < - String conversion. | Excel Programming | |||
| String to Array Conversion | Excel Programming | |||
Hybrid Mode
