Return Word(s) from the RIGHT hand side of a string

[Naughty Norbert]

Hi Guys,
I've been searching to find a solution for my problem and this site seems to be the most suitable to ask my question. I have seen it asked before, but the solutions don't actually provide the required outcome. So here goes:
I have a long list of names and email addresses that I wish to convert into columns of first, last and email. I'm ok with splitting off the email address but have been stumped by the variety of the names.
1 Name and Email First Name(s) Last Name email Address
2 Bob Bobski Bob@BobSky.com Bob Bobski Bob@bobsky.com
3 Harriet Hartebeest BeestlyHarry@Gmail.com Harriet Hartebeest BeestlyHarry@Gmail.com
4 John and Joan Bubble-Head JanjGazprom@Oilwell.com John and Joan Bubble-Head JanjGazprom@Oilwell.com
5 Herb and Daisy Flowers FlowerPower@GMCrops.co.uk Herb and Daisy Flowers FlowerPower@GMCrops.co.uk
6 Willy and Fanny Bottomley The Bumlies@gmail.com Willy and Fanny Bottomley The Bumlies@gmail.com
7 V. Sharp DangerousMan@gmail.com V. Sharp DangerousMan@gmail.com
etc.
As I say, I can split the column up through a simple formula: =MID(A2,FIND(" ",A2,1),100) But this cannot give me the surname on lines 4-6. What I need is a formula that finds the first space from the right hand side of the string and returns it. I would envisage the pseudo-code being from [cell] find last space char and return all characters after it. I just don't know how to do it. I would be very happy if someone could give me the solution.

 

[kweaver]

What do you want the result to be when there's an "and" between the names?

It would be helpful if you used XL2BB to show your data and sample result(s).

Maybe this? (one email isn't valid, so I adjusted it)

Book3
	A	B	C
2	Bob Bobski Bob@BobSky.com Bob Bobski Bob@bobsky.com	Bob Bobski	Bob@bobsky.com
3	Harriet Hartebeest BeestlyHarry@Gmail.com Harriet Hartebeest BeestlyHarry@Gmail.com	Harriet Hartebeest	BeestlyHarry@Gmail.com
4	John and Joan Bubble-Head JanjGazprom@Oilwell.com John and Joan Bubble-Head JanjGazprom@Oilwell.com	John and Joan Bubble-Head	JanjGazprom@Oilwell.com
5	Herb and Daisy Flowers FlowerPower@GMCrops.co.uk Herb and Daisy Flowers FlowerPower@GMCrops.co.uk	Herb and Daisy Flowers	FlowerPower@GMCrops.co.uk
6	Willy and Fanny Bottomley The_Bumlies@gmail.com Willy and Fanny Bottomley The_Bumlies@gmail.com	Willy and Fanny Bottomley	The_Bumlies@gmail.com
7	V. Sharp DangerousMan@gmail.com V. Sharp DangerousMan@gmail.com	V. Sharp	DangerousMan@gmail.com
Sheet7

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISERROR(FIND("and", A2)),TEXTBEFORE(A2," ",2),TEXTBEFORE(A2," ",4))
C2:C7	C2	=IF(ISERROR(FIND("and", A2)),TEXTAFTER(A2," ",5),TEXTAFTER(A2," ",9))

 

[Naughty Norbert]

Seems the website has stripped out the spaces I used to make the 'data' more readable. Now it looks like a duplication. The second set was supposed to look as if it was in separate columns. I've uploaded a screen grab of my data

 

[kweaver]

I'm not sure what your data is based on your first post then the screen shot. Again, please use XL2BB to illustrate.

 

[Naughty Norbert]

Hi K. Thanks for you response - the first names would need to be in a separate column. I'm OK with the 'ands' as they stand,
Your solution brings the names out beautifully. . Thank you very much.
Now How do I extract the last names from the sub set this produces?

 

[Naughty Norbert]

Very Impressed with this XL2BB thing. Here is a simplified version of m y problem. You'll see the 'and' issue quite well here.

Membership.csv
	B	C	D
62	Ben Evertsson	Ben	Evertsson
63	Paul Batty	Paul	Batty
64	Rosie Booth	Rosie	Booth
65	John and Pat Currie	John	and Pat Currie
66	Les and Angie Kerkham	Les	and Angie Kerkham
67	Terry and Lynda Logan	Terry	and Lynda Logan
68	Chris Tina and Emily Matthews	Chris	Tina and Emily Matthews
69	Edwin Page	Edwin	Page
70	Rodney and Hazel Roberts	Rodney	and Hazel Roberts
71	Katy Steventon	Katy	Steventon
72	Joan Whitaker	Joan	Whitaker
73	Brian and Anne Wilks	Brian	and Anne Wilks
Membership

Cell Formulas
Range	Formula
C62:C73	C62	=LEFT(B62,FIND(" ",B62,1))
D62:D73	D62	=RIGHT(B62,LEN(B62)-FIND(" ",B62))

 

[Alex Blakenburg]

If @kweaver's solution worked for you (ie you have TextAfter and Before) just leverage off that.

Book1
	B	C	D
62	Ben Evertsson	Ben	Evertsson
63	Paul Batty	Paul	Batty
64	Rosie Booth	Rosie	Booth
65	John and Pat Currie	John and Pat	Currie
66	Les and Angie Kerkham	Les and Angie	Kerkham
67	Terry and Lynda Logan	Terry and Lynda	Logan
68	Chris Tina and Emily Matthews	Chris Tina and Emily	Matthews
69	Edwin Page	Edwin	Page
70	Rodney and Hazel Roberts	Rodney and Hazel	Roberts
71	Katy Steventon	Katy	Steventon
72	Joan Whitaker	Joan	Whitaker
73	Brian and Anne Wilks	Brian and Anne	Wilks
Sheet1

Cell Formulas
Range	Formula
C62:C73	C62	=TEXTBEFORE(TRIM(B62)," ",-1)
D62:D73	D62	=TEXTAFTER(TRIM(B62)," ",-1)

 

[Fluff]

Maybe

Fluff.xlsm
	A	B	C	D
1
2	Bob Bobski Bob@BobSky.com	Bob	Bobski	Bob@BobSky.com
3	Harriet Hartebeest BeestlyHarry@Gmail.com	Harriet	Hartebeest	BeestlyHarry@Gmail.com
4	John and Joan Bubble-Head JanjGazprom@Oilwell.com	John and Joan	Bubble-Head	JanjGazprom@Oilwell.com
5	Herb and Daisy Flowers FlowerPower@GMCrops.co.uk	Herb and Daisy	Flowers	FlowerPower@GMCrops.co.uk
6	Willy and Fanny Bottomley The_Bumlies@gmail.com	Willy and Fanny	Bottomley	The_Bumlies@gmail.com
7	V. Sharp DangerousMan@gmail.com	V.	Sharp	DangerousMan@gmail.com
Summary

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTBEFORE(A2,C2)
C2:C7	C2	=TEXTAFTER(TEXTBEFORE(A2," ",-1)," ",-1)
D2:D7	D2	=TEXTAFTER(A2," ",-1)

 

[Alex Blakenburg]

@Fluff, the formula in B2 (first name) is leaving the space at the end and will need a Trim added to it (or a space added to the front of C2)

=TRIM(TEXTBEFORE(A2,C2))