Using regexp in VBA

[JRRyan]

OK I'm definitely moving well out of my depth here now, but I can't find any significantly useful info online to help me out.

I have a dynamic list of data that includes headings and subheadings. I need to display just the headings on another worksheet and I need to strip the first section of the head off and just display the second half.

The headings are in the following format:

TEAM A1A - Team Name

The word team is obviously the same length for all, but the A1A code can be 2 or 3 characters long, and can be a mix of letters and numbers. The dash is always the same, but the team name could be 2, 3, or 4 worlds long. So, using a function like MID() doesn't work (not easily anyway).

I have been looking at Regular Expressions, and I think using something like rePostMatch could help me to strip out the team name, but I have no idea how to get this to work in a function and frankly I'm getting totally confused between RegExp and regExpr in vbscript. Any help GREATLY appreciated!

 

[RoryA]

You shouldn't need regexp for that - a formula will do it:
=trim(mid(A1,search("-",A1)+1,len(A1)))

 

[JRRyan]

Ah, See! Why couldn't I think of that! makes perfect sense.

I'll use that formula, thanks for the assist.

Would still be interested in the proper regexp though, now that I've got into it.

 

[Richard Schollar]

This would be one way:

Function GetTeam(s As String)
With CreateObject("vbscript.regexp")
    .ignorecase = True
    .Pattern = "TEAM ...? - "
    GetTeam = .Replace(s, "")
End With
End Function

 

[GTO]

Here is what I had come up with...

Before in Col A, After in B and C:

Excel Workbook
	A	B	C
1	TEAM A1A - Team Name	Team Name	Team Name
2	TEAM B11C - My Team	My Team	My Team
3	TEAM C - Your Team	Your Team	Your Team
4	TEAM DDDD - My Other Team	My Other Team	My Other Team
5	TEAM F - You have another team?	You have another team?	You have another team?
Sheet3

Excel 2003

Sub exa()
Dim MyRange As Range, Cell As Range
    
Dim REX As Object
    
    Set MyRange = Range("A1:A5")
    
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .Pattern = ".+?\-"
        
        For Each Cell In MyRange
            If .Test(Cell.Value) Then
                Cell.Offset(, 1).Value = Trim(.Replace(Cell.Value, vbNullString))
            End If
        Next
    End With
    
    For Each Cell In MyRange
        If CBool(InStr(1, Cell.Value, "-")) Then
            Cell.Offset(, 2).Value = Trim(Mid(Cell.Value, InStr(1, Cell.Value, "-") + 1))
        End If
    Next
End Sub