Convert from string to double no matter the locale
in [Visual Studio]
|
From: Fernando Rodriguez on 14 Nov 2007 11:24 Hi, The result from CSng("3.5") apparently depends on the locale. If I try it on a Spanish PC, I'll get 35 back instead of 3.5 I can "fix" it by call CDbl() on "3,5" instead, but I need this to work with both "3.5" and "3,5" no matter the locale. How can I have this conversion done correctly for both cases?
From: Steve Gerrard on 14 Nov 2007 21:11 "Fernando Rodriguez" <fernandoREMOVE_THIS(a)easyjob.net> wrote in message news:a33bd843d9ec8c9f4fa49953282(a)nntp.aioe.org... > > Hi, > > The result from CSng("3.5") apparently depends on the locale. If I try it on a > Spanish PC, I'll get 35 back instead of 3.5 I can "fix" it by call CDbl() on > "3,5" instead, but I need this to work with both "3.5" and "3,5" no matter the > locale. > > How can I have this conversion done correctly for both cases? > > When your program is running in a locale that uses a comma for decimals, there should not be any strings of the form "3.5". Where are those coming from, or why do you have them in your program?
From: Dag Sunde on 15 Nov 2007 01:28 "Steve Gerrard" <mynamehere(a)comcast.net> wrote in message news:JtmdncPY64ZeN6banZ2dnUVZ_hisnZ2d(a)comcast.com... > > "Fernando Rodriguez" <fernandoREMOVE_THIS(a)easyjob.net> wrote in message > news:a33bd843d9ec8c9f4fa49953282(a)nntp.aioe.org... >> >> Hi, >> >> The result from CSng("3.5") apparently depends on the locale. If I try it >> on a Spanish PC, I'll get 35 back instead of 3.5 I can "fix" it by call >> CDbl() on "3,5" instead, but I need this to work with both "3.5" and >> "3,5" no matter the locale. >> >> How can I have this conversion done correctly for both cases? >> >> > > When your program is running in a locale that uses a comma for decimals, > there should not be any strings of the form "3.5". Where are those coming > from, or why do you have them in your program? > From confused users, I guess... The (pseudo) code below is far from solid enough, but is a starting point Function num2num( byval v as string) as double v = Trim$(v) v = Replace(v, ",", ".") v = Replace(v, " ", "") num2num = CDbl(Val(v)) End Function -- Dag.
From: Bert van den Dongen on 15 Nov 2007 03:54 I solved that problem by introducing a global variable: Public European as Boolean and then setting it True/False by: Temp = Format(123 / 100, "0.00") European = (Mid(Temp, Len(Temp) - 2, 1) = ",") I also have a procedure which checks if a string is numeric, which means: - There may be a "-" as the first character only. - The other characters may only be ",", "." and "0-9". - There may only be ONE "." or ",". - If European I convert "." to "," else "," to ".". Hope this helps. Bert. "Dag Sunde" <me(a)dagsunde.com> schreef in bericht news:473be720(a)news.broadpark.no... > > "Steve Gerrard" <mynamehere(a)comcast.net> wrote in message > news:JtmdncPY64ZeN6banZ2dnUVZ_hisnZ2d(a)comcast.com... >> >> "Fernando Rodriguez" <fernandoREMOVE_THIS(a)easyjob.net> wrote in message >> news:a33bd843d9ec8c9f4fa49953282(a)nntp.aioe.org... >>> >>> Hi, >>> >>> The result from CSng("3.5") apparently depends on the locale. If I try >>> it on a Spanish PC, I'll get 35 back instead of 3.5 I can "fix" it by >>> call CDbl() on "3,5" instead, but I need this to work with both "3.5" >>> and "3,5" no matter the locale. >>> >>> How can I have this conversion done correctly for both cases? >>> >>> >> >> When your program is running in a locale that uses a comma for decimals, >> there should not be any strings of the form "3.5". Where are those coming >> from, or why do you have them in your program? >> > > From confused users, I guess... > > The (pseudo) code below is far from solid enough, but is a starting point > > Function num2num( byval v as string) as double > > v = Trim$(v) > v = Replace(v, ",", ".") > v = Replace(v, " ", "") > > num2num = CDbl(Val(v)) > > End Function > > -- > Dag. >
From: Rick Rothstein (MVP - VB) on 15 Nov 2007 05:09
>I solved that problem by introducing a global variable: > Public European as Boolean > > and then setting it True/False by: > > Temp = Format(123 / 100, "0.00") > European = (Mid(Temp, Len(Temp) - 2, 1) = ",") > > I also have a procedure which checks if a string is > numeric, which means: > - There may be a "-" as the first character only. > - The other characters may only be ",", "." and "0-9". > - There may only be ONE "." or ",". > - If European I convert "." to "," else "," to ".". Here are some functions that I developed and posted awhile ago for the compiled VB world, but which should work fine in Excel's VBA world as well. The Regional Settings functions will return the indicated local settings for the computer they are run on. The Number Checkers will tell you if a string has the form of a valid number. Rick REGIONAL SETTINGS ================================== DateSeparator = Format$(0, "/") DecimalPoint = Format$(0, ".") ThousandsSeparator = Mid$(Format$(1000, "#,###"), 2, 1) Function DateFormat() As String DateFormat = CStr(DateSerial(2003, 1, 2)) DateFormat = Replace(DateFormat, "2003", "YYYY") DateFormat = Replace(DateFormat, "03", "YY") DateFormat = Replace(DateFormat, "01", "MM") DateFormat = Replace(DateFormat, "1", "M") DateFormat = Replace(DateFormat, "02", "dd") DateFormat = Replace(DateFormat, "2", "d") DateFormat = Replace(DateFormat, MonthName(1), "MMMM") DateFormat = Replace(DateFormat, MonthName(1, True), "MMM") End Function Function TimeFormat() As String TimeFormat = CStr(TimeSerial(13, 22, 44)) TimeFormat = Replace(TimeFormat, "22", "mm") TimeFormat = Replace(TimeFormat, "44", "ss") If InStr(TimeFormat, "13") > 0 Then TimeFormat = Replace(TimeFormat, "13", "HH") If InStr(CStr(TimeSerial(1, 22, 44)), "0") = 0 Then TimeFormat = Replace(TimeFormat, "HH", "H") End If Else TimeFormat = Replace(TimeFormat, "1", "h") TimeFormat = Replace(TimeFormat, "0", "h") TimeFormat = Replace(TimeFormat, "PM", "tt", , , vbTextCompare) End If End Function NUMBER CHECKERS ================================== Here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) > 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) > 0 And Value <> "." And _ Value <> vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) > 0 And Value <> DP And _ Value <> vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) > 0 And Value <> DP And _ Value <> vbNullString End Function |