Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Convert Decimal With Comma To Decimal With Point

  1. #1
    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. #2
    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. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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) 
    
    
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: August 8th, 2008, 18:17
  2. Number Format To Replace Decimal With Comma
    By nayone in forum EXCEL HELP
    Replies: 1
    Last Post: March 22nd, 2008, 11:11
  3. Custom Format To Replace Decimal With Comma
    By davidyu83 in forum EXCEL HELP
    Replies: 4
    Last Post: March 22nd, 2008, 06:45
  4. Number Format: Change Decimal to Comma
    By corkyo4 in forum EXCEL HELP
    Replies: 3
    Last Post: November 2nd, 2006, 14:18
  5. Cell Changes Decimal to Comma
    By abygorb in forum EXCEL HELP
    Replies: 22
    Last Post: September 4th, 2006, 14:26

Bookmarks

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