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!
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.
The combination of CStr and Replace will do the job.
Function Dbl2Str(dbl As Double) As String
Dbl2Str = Replace(CStr(dbl), ".", ",")
End Function
something like this then
Dim somestring As String
Dim someDec As Double
someDec = 1.5
somestring = CStr(someDec)
somestring = Replace(somestring, ".", ",")
MsgBox (somestring)
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
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
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