Extract names before and after a /

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
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...

Code:
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:

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

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

Code:
=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.
 
I would like to thank everyone for their time and input, it is very much appreciated.

I am going to go with Peter_SSs all in one formula which does exactly what I want it to do.

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

I like the code Rick Rothstein, it's short, compact and it does exactly what it says on the tin.

One question please Rick Rothstein, I ran Sektor's code and nothing appeared to happen. You mention not using the RegExp macro, has this done anything to my computer and can I undo it please?

Thanks in advance.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
One question please Rick Rothstein, I ran Sektor's code and nothing appeared to happen. You mention not using the RegExp macro, has this done anything to my computer and can I undo it please?
His code doesn't do anything other than load up three variable and then the macro ends. I think he was assuming only one cell had data in it and the OP would add code at the end of the macro to do something with the three variables.
 
Upvote 0
I would like to thank everyone for their time and input, it is very much appreciated.

I am going to go with Peter_SSs all in one formula which does exactly what I want it to do.
You are very welcome. Glad it helped.
 
Upvote 0
I would like to thank everyone for their time and input, it is very much appreciated.

You're welcome, glad to help.
@Peter_SSs, yeah, I was at a friend's place using an antique computer with a lot of distractions, looking back at my formula, I don't know why I used a couple of Extra SUBSTITUTEs, thanks for shortening it for me.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top