Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Decimal With Comma To Decimal With Point

1. Member
Join Date
18th February 2005
Posts
58

## Convert Decimal With Comma To Decimal With Point

I was making a form where the user could enter a number; eg. 4.61

However, depending on the user's background he might enter this as 4,61 (comma) or as 4.61

In the form this variable comes in as a string. How can I convert this in VBA to a digit if I don't know in advance if the users system uses a . or , as the digit-seperator??

Any help would be very grateful

I thought that in access you can actually predefine the type that is entered into the form but I don't believe this is possible in Excel (or at least i could not find something like that)

thanks for the help

---
<snip by admin. Read the rules you have agreed to or you may be banned>
Last edited by Dave Hawley; August 24th, 2008 at 09:50.

Excel Video Tutorials / Excel Dashboards Reports

2. I agreed to these rules
Join Date
6th September 2007
Posts
5

## Re: Forms Entry With Comma Or Point

VB:
MsgBox CDbl(Me.TextBox1.Value)
Or
MsgBox CDbl(Replace(Me.TextBox1.Value, ",", ""))

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Forms Entry With Comma Or Point

VB:
CDbl(Replace(Me.TextBox1.Value, ",", [COLOR="Red"]"."[/COLOR]))

However, the Replace should not be necessary; the type conversion functions consider the locale settings on the computer, so maybe just
VB:
CDbl(Me.TextBox1.Value)

4. ## Re: Forms Entry With Comma Or Point

IF the textbox value is a number, you shouldn't need any conversion so long as you force the Value property of the TextBox

5. I agreed to these rules
Join Date
3rd June 2011
Posts
1

## Re: Convert Decimal With Comma To Decimal With Point

I agree, this is a real pain. I use something like the following:

' This function corrects a very annoying feature of Excel. Depending on the language, decimal points can be
' set to either comma's or points. This small function tests whether the same result is achieved when the
' comma in a string/variant is compared to the same numeric string/variant when a decimal point is used.
' If the result is different, it ASSUMES THAT THE COMMA IS INCORRECT and replaces it with a '.'
'
' It is called using:
' Result = Correct_Decimal_Usage(str1)
' where str1 is e.g. "34,78" and Result is dimensioned as a Double
'
VB:
Function Correct_Decimal_Usage(ThisNum As Variant) As Double

Dim Answ As Double

If (CDbl(Replace(ThisNum, ",", "."))) <> CDbl(ThisNum) Then
'    MsgBox "ThisNum " & CDbl(ThisNum) & " caused a problem!" & vbNewLine & " Should be: " & CDbl(Replace(ThisNum, ",", "."))
Answ = CDbl(Replace(ThisNum, ",", "."))
Else
'    MsgBox "str1 " & CDbl(ThisNum) & " is fine!"
Answ = CDbl(ThisNum)
End If

End Function

If you wanted to be really clever, you could extend this so that it works for both comma2point and point2comma cases.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno