Clever Geek Handbook

VBA Double as String with comma, not period

I'm looking for a solution to convert double to string, but the string must have a comma before the decimal point, not a point.

"One and a half" should look like this: 1.5 (German notation).

Thanks for the help!

+4


source to share


6 answers


Unfortunately, in VBA, you cannot write language code easily. That is, you cannot specify the locale when you are casting CStr

.

One job is to convert a double of 0.5 to a string and see what you get. If you're done with 0,5

, then you are in German (etc.) and you don't have to do anything.



If you're done with 0.5

, then you know that you need to do the conversion. Then you just need to traverse the string, replacing the periods with commas and vice versa (the opposite bit will matter if your string contains thousands of separators). You can use Replace

for this.

+4


source


The combination of CStr and Replace will do the job.



Function Dbl2Str(dbl As Double) As String
    Dbl2Str = Replace(CStr(dbl), ".", ",")
End Function
+3


source


something like this then

Dim somestring As String
Dim someDec As Double

someDec = 1.5
somestring = CStr(someDec)
somestring = Replace(somestring, ".", ",")
MsgBox (somestring)
+1


source


Following on from RubberDuck's 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
+1


source


Select the cells you want to convert and run this little 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 "." they will change, and they will be strings, not paired

0


source


I checked the other answers, but ended up writing my own solution for converting custom data like 1500.5

to 1,500.50

using the following 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
0


source






More articles:


All Articles