How to find nth space, and return everything to the left

[mmille10]

Hi Guys,

I am trying to seperate first names from the last name. I am able to do it for the single first and last names, but when i have multiple firsts i am not able to do so... see below example;

Joe Blow

=LEFT(E4,FIND(" ",E4,1)) - returns Joe

But when i have Joe & Jane Blow
it only returns joe ?

Your help is appreciated.

Thanks

 

[Aladin Akyurek]

Something like...

=TRIM(LEFT(E4,FIND("|",SUBSTITUTE(E4," ","|",LEN(E4)-LEN(SUBSTITUTE(E4," ",""))))))