Regular Expressions in Excel using VB

Dr Nic · 06-09-2011 08:41PM #1

Hi Guys,
I need to do some regex using excel. I've used regex for years with vim/linux but now the only programming tool i can use is excel.

Its giving me lots of grief

My text is just "him" in A1.

Here is my code

Sub e()
 
Dim reg As New RegExp
    Dim rng As Range, i As Long, j As Long
    Dim mtch As MatchCollection, mt As match
 
    reg.IgnoreCase = True
    reg.MultiLine = False
    reg.Pattern = "{hi}"
 
    Set mtch = reg.Execute(Range("A" & 1).Value)
    Range("B" & 1).Value = Range("A" & 1)
    Range("C" & 1).Value = mtch.Count
 
    For i = 0 To mtch.Count - 1
        Range("D" & i).Value = mtch(i)  'place results into arbitary cells
 
    Next
End Sub

It outputs him in B:1, 0 in C:1 and nothing else.
I've tried lots of different combo's

Any idea's?
Thanks!!

Malice · 07-09-2011 05:25PM

What exactly are you trying to get the regular expression to do?

EL_Loco · 07-09-2011 05:34PM

that line is making b1 = a1, so it will always be "him" or whatever you put in a1

Range("B" & 1).Value = Range("A" & 1)

D0 doesn't exist as a cell, so your first line of the loop will fail

For i = 0 To mtch.Count - 1
        Range("D" & i).Value = mtch(i)  'place results into arbitary cells

try

Range("D" &i+1).value

your C1 is zero because you're not getting any matches, I'm not too well up on reg ex, so I don't know where it's looking at for matches to "him". But there's a few pointers. Hope it helps.

srsly78 · 07-09-2011 05:53PM

Use the built-in regex library. Tools -> references -> Microsoft VBscript Regular Expressions 5.5 (you get with office 2007 anyway).

edit: oh, you are

Thought you were checking manually for a sec... Looking at code more, I have no idea what you are trying to do. So here is the manual: http://msdn.microsoft.com/en-us/library/ee236359(v=vs.85).aspx

Dr Nic · 07-09-2011 10:54PM

I just wanted to match any character initially and print it out. Wouldnt work for 3 hours...

All's working fine now except for matching 'any' character.
In other implementations of regex .+ would match 1 or more of any character
This wont work in excel vb regex for some reason
Anyone know?
Thanks!

Dr Nic · 07-09-2011 10:55PM

Current code

Sub e()


    Dim rng As Range, i As Long, j As Long
    
    '----------------------------------------------------------------------------------------------------
    Dim regDate As New RegExp
    Dim mtchDate As MatchCollection
    regDate.Pattern = "(\d+\/\d+\/\d+)\s+(\d+\/\d+\/\d+)\s+(\d+\/\d+\/\d+)\s+(\d+\/\d+\/\d+)\s+(\d+\/\d+\/\d+)\s+(\d+\/\d+\/\d+)"
    '------------------------------------------------------------------------------------------------------
    
    Set mtchDate = regDate.Execute(Range("A" & 1).Value)
    'Range("B" & 1).Value = Range("A" & 1)
    'Range("A" & 2).Value = mtch.Count
    Range("B" & 1).Value = mtchDate(0).SubMatches(0)
    Range("C" & 1).Value = "'" + mtchDate(0).SubMatches(1)
    Range("D" & 1).Value = "'" + mtchDate(0).SubMatches(2)
    Range("E" & 1).Value = "'" + mtchDate(0).SubMatches(3)
    Range("F" & 1).Value = "'" + mtchDate(0).SubMatches(4)
    Range("G" & 1).Value = "'" + mtchDate(0).SubMatches(5)
    
    Dim regVal As New RegExp
    Dim mtchVal As MatchCollection
    regVal.Pattern = "(\S+)\s*\|"
    
    For i = 6 To 46
      Set mtchVal = regVal.Execute(Range("A" & i).Value)
      Range("B" & i - 4).Value = mtchVal(0).SubMatches(0)
    Next


End Sub

Regular Expressions in Excel using VB

Comments