Hello TC,
You could do this:
Private Sub Test()
Dim CurString As String = "$1.29"
Dim CurString2 As String = "EUR 3.33"
Dim CurDecimal As Decimal = FormatCurrency(CurString)
Dim CurDecimal2 As Decimal = FormatCurrency(CurString2)
End Sub
Private Function FormatCurrency(ByVal CurString As String) As Decimal
Dim tmp As String = Replace(CurString, "$", "")
Dim retVal As Decimal
'Uncomment the following statement if you use "," for decimal.
'tmp = Replace(tmp, ".", ",")
Try
retVal = CDec(tmp)
Catch
retVal = FormatCurrency2(tmp)
End Try
Return retVal
End Function
Private Function FormatCurrency2(ByVal CurString As String) As Decimal
Dim tmp As String = "", x As String
Dim t As Integer
For t = 1 To Len(CurString)
x = Strings.Mid(CurString, t, 1)
If IsNumeric(x) OrElse x = "." Then
tmp &= x
End If
Next
If Len(tmp) > 0 Then
Return CDec(tmp)
Else
Return 0D
End If
End Function
If you have few currencies in your list, you could add these currencies
to the FormatCurrency function.
Please note that if you have to format many currencies, this might
become a slowdown, if most currency strings are "$".
Let's say, you have 100 currency strings. 90 of them are $, the other 10
are EUR. If you add "EUR" to FormatCurrency, no matter what currency you
have, it will always try to replace "EUR" - meaning in 90 attempts this
will fail (as it is $).
This is where FormatCurrency2 comes in. Let's say, you have a "EUR"
currency string. FormatCurrency will fail to replace it (as it just
replaces "$"). When trying to convert it to Decimal, it will branch to
the Catch statement and call FormatCurrency2.
FormatCurrency2 will check which characters in the string are numeric
(0...9) or decimal point and then concatenate them. After that it will
convert it to Decimal or if there were no numeric values return 0.0.
But there is one catch (at present): If your Windows is set up to use
the "," instead of the "." for decimals, you will receive wrong values.
3.33 becomes 333. Therefore, you would have to replace the "." with ","
Just uncomment the Replace statement in FormatCurrency to do that.
If you have many different currencies to deal with, then perhaps
FormatCurrency3 is something for you:
Private Function FormatCurrency3(ByVal CurString As String) As Decimal
Dim Currencies() As String = {"$", "EUR", "€", "¥", "£", "GBP", "RMB"}
Dim tmp As String = CurString
Dim retVal As Decimal
Dim t As Integer
'Uncomment the following statement if you use "," for decimal.
'tmp = Replace(tmp, ".", ",")
For t = 0 To UBound(Currencies)
If InStr(tmp, Currencies(t)) > 0 Then
tmp = Replace(tmp, Currencies(t), "")
Exit For
End If
Next
Try
retVal = CDec(tmp)
Catch
retVal = FormatCurrency2(tmp)
End Try
Return retVal
End Function
For an additional speed increase, I would put the Currencies array on
class level so that the array is not created over and over again.
The difference to the previous approach is that in the For...Next loop
it first checks if a currency is used to avoid an unneccessary Replace
attempt. Also here, in case the attempt to convert it to decimal fails,
the string will be handed over to FormatCurrency2.
Best regards,
Martin
