VBA to remove ANY currency symbol?

[PoggiPJ]

I have an International application where I need to remove any currency symbol from a User Form field (Me.Inventory.Value) prior to saving the value in a worksheet.

Since I don't know the user's currency symbol in advance, the code needs to check Me.rcfCurrentYearInventory.Value from a string of possible currency symbols and convert to nothing "".

Would something like this work, or is there a better method?

Me.Inventory.Value = WorksheetFunction.Substitute(Me.Inventory.Value, "$£€¥", "")
Recalc.Range("YTDInventory").Cells(1, 2).Value = Me.Inventory.Value

 

[GTO]

Sorry, not an answer, just a question. Would I be way off in thinking that the only thing that should be allowed, would be 0-9 and maybe a decimal mark?

 

[Scott Huish]

How about:

Sub test()
Dim strCurrencySymbol As String
strCurrencySymbol = Application.International(xlCurrencyCode)
Me.inventory.Value = Replace(Me.inventory.Value, strCurrencySymbol, "")
End Sub

 

[PoggiPJ]

Sorry, not an answer, just a question. Would I be way off in thinking that the only thing that should be allowed, would be 0-9 and maybe a decimal mark?

Hi GTO, I think that might be true. However in Euros the decimal marks off thousands, and the comma indicates cents.

 

[PoggiPJ]

How about:

Sub test()
Dim strCurrencySymbol As String
strCurrencySymbol = Application.International(xlCurrencyCode)
Me.inventory.Value = Replace(Me.inventory.Value, strCurrencySymbol, "")
End Sub

Interesting HotPepper, i'll give this a try. I didn't know there was an "international" object under Application.

 

[PoggiPJ]

Update: I tried it - it didn't work for my test, because the sample value contained a British Sterling symbol, and my test (being in the US) tested for a "$". So it should work in the user's native country - but if they send the file to another country it might not. I wish there was a way I could test for ANY currency symbol, and replace it

 

[GTO]

Hi GTO, I think that might be true. However in Euros the decimal marks off thousands, and the comma indicates cents.

Hi Peter,

I do not deal with conversion issues, so a stab at it

If only U.S. vs English conversions, I'm sure you can simplify. Well actually, I'm sure anyone who is familiar with these type issues and/or RegExp may well offer markedly improved, but here's my try...

Option Explicit
    
Sub exa1()
Dim sInput As String, dblInput As Double
    
    '// I just picked a couple of 'odd' characters to strip //
    sInput = Chr(163) & Chr(162) & "1.123,01" & Chr(163) & Chr(162)
    
    If StripIt(sInput, dblInput) Then
        MsgBox dblInput
    End If
    
End Sub
    
Function StripIt(InputString As String, ReturnDouble As Double) As Boolean
Dim CharPos As Long
Dim sTmp As String
Dim bolFixed As Boolean
    
Static REX As Object '<--- RegExp
    
    If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
    
    With REX
        .Global = True
        '// Match anything OTHER than 0-9, a comma or a decimal //
        .Pattern = "[^0-9,.]"
        '// Then kill any other chars                           //
        InputString = .Replace(InputString, vbNullString)
        '// Match only a comma or a decimal point               //
        .Pattern = "[,.]"
        '// Check the last three spots to see if we have a decimal indicator of dot or comma//
        For CharPos = Len(InputString) To Len(InputString) - 2 Step -1
            sTmp = Mid(InputString, CharPos, 1)
            If .Test(sTmp) Then
                '// If we find decimal indicator, replace with unusual character            //
                InputString = Mid(InputString, 1, CharPos - 1) & _
                              Replace(InputString, sTmp, Chr(230), CharPos)
                '// Then (crossing my fingers a bit...) eliminate any thousands seperators  //
                '// and replace our unusual marker with the dot.                            //
                .Pattern = "[^0-9" & Chr(230) & "]"
                InputString = .Replace(InputString, vbNullString)
                InputString = Replace(InputString, Chr(230), Chr(46))
                bolFixed = True
                Exit For
            End If
        Next
            
        If Not bolFixed Then InputString = .Replace(InputString, vbNullString)
    End With
    
    '// If what we have left can be a double, we must have succeeded (okay, still fingers crossed)//
    On Error Resume Next
    ReturnDouble = CDbl(InputString)
    If Not Err Then StripIt = True
    On Error GoTo 0
End Function

Hope that helps a little,

Mark

 

[PoggiPJ]

Thank you Mark! I'm working my way through trying to follow it.