Extracting Firstnames

[JEB85]

Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m trying to extract first and last names. I have the Firstnames cracked but am struggling with the last name – the reason being is that some people have middle names and others don’t.<o></o>
<o></o>
The set up is Surname, Firstname Middlename OR Surname, Firstname<o></o>
<o></o>
In the example below my data set (including headers) runs from A1:C6. Column C shows the firstnames I’m after.<o></o>

<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=342><COLGROUP><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 157pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20 width=209>Names</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=62>Surname</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=71>FirstName</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>SMITH, TOM</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">SMITH</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">TOM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>BLOGS, JO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">BLOGS</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">JO</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>SMITH, PAUL JAMES</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">SMITH</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">PAUL</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>COOL, STEVE</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">COOL</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">STEVE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>MARTIN, BEN SMITH</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">MARTIN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">BEN</TD></TR></TBODY></TABLE>

The formula I’ve used to get first names:=LEFT(A2,FIND(",",A2)-1)<o></o>
<o></o>
Thanks<o></o>

 

[Ruddles]

The formula I’ve used to get first names:=LEFT(A2,FIND(",",A2)-1)

That extracts the surname rather than the first name, Shirley?

You can extract the first given name as follows:-

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),50,100))

 

[JEB85]

"That extracts the surname rather than the first name, Shirley?" ha ha, sorry Ruddles you're right, I meant first name!!

Thanks for the formula, it's brilliant!

 

[GlennUK]

Hey Ruddles, ( and Jeb ), what about surnames with embedded spaces, like:
Van Der Valk
?

 

[Ruddles]

There were no surnames like that in the data sample!

But you're 100% correct: the above formula won't work if there are spaces in the surname. Thank you for spotting this.

I'll have another go...

=TRIM(MID(SUBSTITUTE(MID(A2&",",FIND(",",A2&",")+1,100)," ",REPT(" ",100)),50,100))

Obviously the requestor should satisfy himself that any offerings work properly before relying on them.

 

[JEB85]

I didn't have any of those occurrences in my data set so the original formula worked well!
Ruddles - just tried your second formula and for SMITH, TOM it's retrieving Tom,
Any ideas?

 

[Ruddles]

Woops, more embarrassment! I was trying to do three things at once and ended up doing none of them very well!

Try:-

=TRIM(MID(SUBSTITUTE(MID([COLOR=red][B]A2[/B][/COLOR],FIND(",",A2&",")+1,100)," ",REPT(" ",100)),50,100))

 

[JEB85]

No worries Ruddles, you got there in the end and it's genius!

Thanks

 

[Ruddles]

The feedback is appreciated.