Can find how to use replace with regex in VBA

Question

I have a table in Excel and one of its columns contains string of the type: 125j, 0j, 12j, etc.

I want the value of the cells (in this column) to be replace by the numbers (i.e. I want to drop the "j") using the VBA. Here is what I have:

Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
reg.IgnoreCase = True
reg.Pattern = "^[0-9]+"

If reg.test(Cells(i, 4).Value) Then
    Cells(i, 4).Value = reg.Execute(Cells(i, 4).Value)
End If

But it doesn't work, do anyone knows either how to crrect it or to obtain the same result from another command?

Thank you

David Zemens · Accepted Answer · 2014-08-07 15:00:48Z

1

The result of the .Execute method is a MatchCollection object. You can use that explicitly:

Dim reg As Object
Dim allMatches As Object

Set reg = CreateObject("VBScript.RegExp")
reg.IgnoreCase = True
reg.Pattern = "^[0-9]+"

If reg.test(cells(i, 4).Value) Then
    Set allMatches = reg.Execute(cells(i, 4).Value)
    cells(i, 4).Value = allMatches(0)
End If

Or implicitly:

    cells(i, 4).Value = reg.Execute(cells(i, 4).Value)(0)

edited Aug 7, 2014 at 15:00

answered Aug 7, 2014 at 14:55

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can find how to use replace with regex in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related