Excel VBA - Remove everything but numbers and dots from string - Stack Overflow

admin管理员组

文章数量:1193751

What is the best approach for a code to remove all text from a cell besides numbers and dots using regex?

I got the basic regex expression "[^.0-9]+)" working on Regex101, but some codes I tried are not working.

I'm getting an empty error box on the first code and "User-defined type not defined" on the second code.

First Code

Sub RegexDelete()

Dim text As String
Dim regEx As Object
Dim test As Object

text = activecell.value

Set regEx = CreateObject("VBScript.Regexp")

regEx.Global = True
regEx.Pattern = "[^.0-9]+)"

Set test = regEx.Execute(text)
MsgBox (test(0).Value)

End Sub

Second code

Sub simpleRegex()
    Dim strPattern As String: strPattern = "[^.0-9]+)"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range
    
    Set Myrange = ActiveSheet.Range("A1")
    
    If strPattern <> "" Then
        strInput = Myrange.Value
        
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
        
        If regEx.test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

What is the best approach for a code to remove all text from a cell besides numbers and dots using regex?

I got the basic regex expression "[^.0-9]+)" working on Regex101, but some codes I tried are not working.

I'm getting an empty error box on the first code and "User-defined type not defined" on the second code.

First Code

Sub RegexDelete()

Dim text As String
Dim regEx As Object
Dim test As Object

text = activecell.value

Set regEx = CreateObject("VBScript.Regexp")

regEx.Global = True
regEx.Pattern = "[^.0-9]+)"

Set test = regEx.Execute(text)
MsgBox (test(0).Value)

End Sub

Second code

Sub simpleRegex()
    Dim strPattern As String: strPattern = "[^.0-9]+)"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range
    
    Set Myrange = ActiveSheet.Range("A1")
    
    If strPattern <> "" Then
        strInput = Myrange.Value
        
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
        
        If regEx.test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

• excel
• vba

Share Improve this question Follow asked Jan 23 at 19:42 BkviegasBkviegas 1977 bronze badges 3

• 2 "User-defined type not defined" indicates you've not added a reference to the regexp library in your VBProject – Tim Williams Commented Jan 23 at 19:45
• ^^^ see here – cybernetic.nomad Commented Jan 23 at 19:47
• Would help to provide a few examples of your expected input and output strings. – Tim Williams Commented Jan 23 at 19:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

Pls try.

Sub RegexDelete()
    
    Dim text As String
    Dim regEx As Object
    
'    text = ActiveCell.Value
    text = "Test2.30String" ' for testing

    Set regEx = CreateObject("VBScript.Regexp")
    
    regEx.Global = True
    regEx.Pattern = "[^.0-9]"
    
    MsgBox regEx.Replace(text, "")
    
End Sub

Worksheet function can do it well if you are using Office365.

本文标签： Excel VBARemove everything but numbers and dots from stringStack Overflow