Extract names before and after a /

[S.H.A.D.O.]

Good evening,

I am an avid darts fan and keep a spreadsheet of the different tornaments.
Now when the draw is shown on the Internet it is the format...

Rob Cross (3) v Steve Lennon/Rowby-John Rodriguez
John Henderson (14) v James Richardson/Jason Cullen
Mensur Suljovic (6) v Josh Payne/Jonny Clayton
Simon Whitlock (11) v Jan Dekker/Adrian Lewis
Joe Cullen (7) v Adam Huckvale/Jermaine Wattimena
Kim Huybrechts (10) v Wayne Jones/Jamie Lewis
Peter Wright (2) v Johan Engstrom/James Wilson
Gerwyn Price (15) v Toni Alcinas/Dragutin Horvat
Michael Smith (4) v Tobias Mueller/Stephen Bunting
Jelle Klaasen (13) v Ricky Evans/Krzysztof Ratajski
Daryl Gurney (5) v James Wade/Cristo Reyes
Mervyn King (12) v Ryan Joyce/Michael Rosenauer
Dave Chisnall (8) v Cameron Menzies/Kevin Munch
Ian White (9) v Christian Kist/Kyle Anderson
Michael van Gerwen (1) v Mike De Decker/Terry Jenkins
Darren Webster (16) v Jeffrey de Graaf/Steve Beaton

...for example.

What I want to be able to do is to split all the names into different columns please.

To split the name on the left I have come up with this formula:

=LEFT(A1,FIND(")",A1,1)*1)

To split the name to the right of the slash I have come up with this formula:

=MID(A1,FIND("/",A1)+1,9999)

But I can't seem to get it to do the name in the middle.

Any help will be greatly appreciated.

Thanks in advance.

 

[Fluff]

Probably better ways, but how about
=MID(A1,FIND("v",A1,1)+2,FIND("/",A1,1)-FIND("v",A1,1)-2)

 

[S.H.A.D.O.]

=MID(A1,FIND("v",A1,1)+2,FIND("/",A1,1)-FIND("v",A1,1)-2)

Brilliant, thanks Fluff.

I just needed to add the space after the v.

MID(A1,FIND("v ",A1,1)+2,FIND("/",A1,1)-FIND("v ",A1,1)-2)

Thanks again and have a great weekend.

 

[jtakw]

Hi,

Try this single formula for extracting all 3 parts.
I'm not at my own computer and do not have the HTML maker on this computer, so I can only show the code.
Assuming your data starts at A1, place formula in B1 copied down as far as needed, and across to Column D.
I've also assumed you wanted to retain the number and brackets after the first set of name string (i.e. (3))

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," v ","^"),"/","^")," ","/"),"^",REPT(" ",100)),COLUMNS($B:B)*100-99,100),"/"," "))

 

[Peter_SSs]

I just needed to add the space after the v.

That would still fail if the first name was, say "Darren Pavlov" or "Gustav Medic"

Here is how I would do it.

Excel Workbook
	A	B	C	D
1	Rob Cross (3) v Steve Lennon/Rowby-John Rodriguez	Rob Cross (3)	Steve Lennon	Rowby-John Rodriguez
2	John Henderson (14) v James Richardson/Jason Cullen	John Henderson (14)	James Richardson	Jason Cullen
3	Mensur Suljovic (6) v Josh Payne/Jonny Clayton	Mensur Suljovic (6)	Josh Payne	Jonny Clayton
4	Simon Whitlock (11) v Jan Dekker/Adrian Lewis	Simon Whitlock (11)	Jan Dekker	Adrian Lewis
5	Joe Cullen (7) v Adam Huckvale/Jermaine Wattimena	Joe Cullen (7)	Adam Huckvale	Jermaine Wattimena
6	Kim Huybrechts (10) v Wayne Jones/Jamie Lewis	Kim Huybrechts (10)	Wayne Jones	Jamie Lewis
7	Peter Wright (2) v Johan Engstrom/James Wilson	Peter Wright (2)	Johan Engstrom	James Wilson
8	Gerwyn Price (15) v Toni Alcinas/Dragutin Horvat	Gerwyn Price (15)	Toni Alcinas	Dragutin Horvat
9	Michael Smith (4) v Tobias Mueller/Stephen Bunting	Michael Smith (4)	Tobias Mueller	Stephen Bunting
10	Jelle Klaasen (13) v Ricky Evans/Krzysztof Ratajski	Jelle Klaasen (13)	Ricky Evans	Krzysztof Ratajski
11	Daryl Gurney (5) v James Wade/Cristo Reyes	Daryl Gurney (5)	James Wade	Cristo Reyes
12	Mervyn King (12) v Ryan Joyce/Michael Rosenauer	Mervyn King (12)	Ryan Joyce	Michael Rosenauer
13	Dave Chisnall (8) v Cameron Menzies/Kevin Munch	Dave Chisnall (8)	Cameron Menzies	Kevin Munch
14	Ian White (9) v Christian Kist/Kyle Anderson	Ian White (9)	Christian Kist	Kyle Anderson
15	Michael van Gerwen (1) v Mike De Decker/Terry Jenkins	Michael van Gerwen (1)	Mike De Decker	Terry Jenkins
16	Darren Webster (16) v Jeffrey de Graaf/Steve Beaton	Darren Webster (16)	Jeffrey de Graaf	Steve Beaton
17	Darren Pavlov (16) v Jeffrey de Graaf/Steve Beaton	Darren Pavlov (16)	Jeffrey de Graaf	Steve Beaton
18	Gustav Medic (16) v Jeffrey de Graaf/Steve Beaton	Gustav Medic (16)	Jeffrey de Graaf	Steve Beaton
Extract names

 

[bkjohn2016]

Click on the Data tab above your ribbon

Click on the text to columns button in the Data Tools Section

Select Delimited

Enter "v" in the others option

Click ok

Repeat for /

 

[Peter_SSs]

Select Delimited

Enter "v" in the others option

Click ok

I don't think that you have thought that through very well.
Why don't you give just your first step a try with the sample data provided?

Your idea of Text to Columns could be used for a fairly quick manual result though ..
- Do a Find/Replace on the column with Find what: " v " (without the quotes but with a space each side of the v), Replace with: / and make sure in Options that 'Match entire cell contents' is not checked.
- Text to Columns -> Delimited -> Other: / -> Finish

 

[Peter_SSs]

Hi,

Try this single formula for extracting all 3 parts.
I'm not at my own computer and do not have the HTML maker on this computer, so I can only show the code.
Assuming your data starts at A1, place formula in B1 copied down as far as needed, and across to Column D.
I've also assumed you wanted to retain the number and brackets after the first set of name string (i.e. (3))

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," v ","^"),"/","^")," ","/"),"^",REPT(" ",100)),COLUMNS($B:B)*100-99,100),"/"," "))

You appear to have a few superfluous substitutes going on in that formula. If a single formula, copied across and down, is desired then this considerably shorter one should do the same job:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," v ","/"),"/",REPT(" ",100)),COLUMNS($B:B)*100-99,100))

 

[Sektor]

Variables name1, name2 and name3 hold the found names:

Sub FindNames()


    Dim name1$, name2$, name3$
    Dim re As Object 'RegExp
    Dim mc As Object 'MatchCollection
    Dim m As Object  'Match


    s = "Rob Cross (3) v Steve Lennon/Rowby-John Rodriguez"
    
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.Pattern = "(.+?)\s+\(\d+\)\s+v\s+(.+?)/(.+)"
    
    Set mc = re.Execute(s)
    If mc.Count > 0 Then
        Set m = mc(0)
        name1 = m.SubMatches(0)
        name2 = m.SubMatches(1)
        name3 = m.SubMatches(2)
    Else
        MsgBox "No match was found", vbExclamation
    End If


End Sub

 

[Rick Rothstein]

Variables name1, name2 and name3 hold the found names:

Sub FindNames()


    Dim name1$, name2$, name3$
    Dim re As Object 'RegExp
    Dim mc As Object 'MatchCollection
    Dim m As Object  'Match


    s = "Rob Cross (3) v Steve Lennon/Rowby-John Rodriguez"
    
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.Pattern = "(.+?)\s+\(\d+\)\s+v\s+(.+?)/(.+)"
    
    Set mc = re.Execute(s)
    If mc.Count > 0 Then
        Set m = mc(0)
        name1 = m.SubMatches(0)
        name2 = m.SubMatches(1)
        name3 = m.SubMatches(2)
    Else
        MsgBox "No match was found", vbExclamation
    End If
End Sub

First of all, I think the OP will end up having multiple cells in Column A to process, not just the one asked about. Secondly, I think the OP will want the split out values placed on the worksheet in adjacent columns. Given that, and assuming the OP is willing to use VBA code, I would propose this non-RegExp macro to do the job...

[table="width: 500"]
[tr]
	[td]Sub FindNames()
  With Columns("B")
    .Offset(, -1).Copy .Cells(1)
    .Replace " v ", "/", xlPart, , False, , False, False
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "/"
  End With
End Sub[/td]
[/tr]
[/table]