4

I am searching for a solution to convert a double to a string, but the string should have a comma before the decimal place, not a point.

"One and a half" should look that way 1,5 (german notation).

Thanks for your help!!

Community
  • 1
  • 1
mrbela
  • 4,477
  • 9
  • 44
  • 79

6 Answers6

5

Unfortunately in VBA, you can't easily write locale-independent code. That is, you can't specify a locale when you take a CStr cast.

One work around is to convert a double like 0.5 to a string and see what you end up with. If you end up with 0,5 then you're in German (etc.) locale, and you don't need to do anything else.

If you end up with 0.5 then you know you need to make a conversion. Then you just need to traverse your string, replacing dots with commas and vice versa (the vice versa bit is important in case your string has thousands delimiters). You can use Replace for that.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • 2
    I don't think that's entirely true. [Language Settings are available through the application object](http://msdn.microsoft.com/en-us/library/office/ff821852%28v=office.15%29.aspx) (in excel at least) and exposed to the client through the [MsoLanguageId Enumeration](http://msdn.microsoft.com/en-us/library/aa432635(v=office.12).aspx). – RubberDuck Jan 12 '15 at 15:48
  • Are you suggesting a solution based on a switch on the MsoLanguageId enumeration? – Bathsheba Jan 12 '15 at 15:54
  • 1
    Not necessarily, but maybe. I just found it a bit dubious to claim that "you can't write locale-independent code". – RubberDuck Jan 12 '15 at 15:56
  • @RubberDuck for non excel functions like Val(), these settings would be ignored. We still would need a way to use these kind of functions without depending on the locale. – TZubiri Jul 18 '16 at 13:46
5

A combination of CStr and Replace will do the job.

Function Dbl2Str(dbl As Double) As String
    Dbl2Str = Replace(CStr(dbl), ".", ",")
End Function
Jeanno
  • 2,769
  • 4
  • 23
  • 31
  • 1
    If you run this in England, you could end up with something like "1,234,56" if the original number was 1234.56 and the user has the thousands delimiter set. – Bathsheba Jan 12 '15 at 16:52
3

Following RubberDuck comment I ended up with this:

Function DblToStr(x As Double)
 DblToStr = CStr(x)

 If (Application.ThousandsSeparator = ".") Then
  DblToStr = Replace(DblToStr, ".", "")
 End If
 If (Application.DecimalSeparator = ".") Then
  DblToStr = Replace(DblToStr, ".", ",")
 End If

End Function
fortuna
  • 276
  • 2
  • 5
1

something like this then

Dim somestring As String
Dim someDec As Double

someDec = 1.5
somestring = CStr(someDec)
somestring = Replace(somestring, ".", ",")
MsgBox (somestring)
exceptional exception
  • 527
  • 7
  • 26
  • If you run this in England, you could end up with something like "1,234,56" if the original number was 1234.56 and the user has the thousands delimiter set. – Bathsheba Jan 12 '15 at 16:53
  • Interesting point, though it was given that the data type to be converted was already a double. I don't believe a comma can exist in a double, but then I don't know if that's also true in Europe. – exceptional exception Jan 12 '15 at 19:40
0

Select the cells you wish to convert and run this small macro:

Sub changeIT()
    For Each r In Selection
        t = r.Text
        If InStr(1, r, ".") > 0 Then
            r.Clear
            r.NumberFormat = "@"
            r.Value = Replace(t, ".", ",")
        End If
    Next r
End Sub

Only those cells with "." in them will change and they will be Strings rather than Doubles

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

I checked the other answers but ended up writing my own solution to convert user inputs like 1500.5 into 1,500.50, using below code:

'
' Separates real-numbers by "," and adds "." before decimals
'
Function FormatNumber(ByVal v As Double) As String
    Dim s$, pos&
    Dim r$, i&

    ' Find decimal point
    s = CStr(v)
    pos = InStrRev(s, ".")
    If pos <= 0 Then
        pos = InStrRev(s, ",")
        If pos > 0 Then
            Mid$(s, pos, 1) = "."
        Else
            pos = Len(s) + 1
        End If
    End If

    ' Separate numbers into "r"
    On Error Resume Next
    i = pos - 3
    r = Mid$(s, i, 3)
    For i = i - 3 To 1 Step -3
        r = Mid$(s, i, 3) & "," & r
    Next i
    If i < 1 Then
        r = Mid$(s, 1, 2 + i) & "," & r
    End If

    ' Store dot and decimal numbers into "s"
    s = Mid$(s, pos)
    i = Len(s)
    If i = 2 Then
        s = s & "0"
    ElseIf i <= 0 Then
        s = ".00"
    End If

    ' Append decimals and return
    FormatNumber = r & s
End Function
Top-Master
  • 7,611
  • 5
  • 39
  • 71
  • If interested in the other way around (converting string to numbers) check [ParseNumber(...)](https://stackoverflow.com/a/56822434/8740349) – Top-Master Jun 30 '19 at 04:52