On Sat, 18 Feb 2006 13:36:30 -0800, "genkate"
wrote:

Yes, it worked great! Is there a way to extract the rest of the name into the
other column?

Same principle using the LEFT function:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))-1)


Also, I found that there was a space after the surname. There
must be a way to delete that without deleting all the other spaces. Thank you
so much!

Is there always a space? Is it a normal space or a no-break space?

If it's a normal space, you could always wrap the Last Name formula in a TRIM
function:

=TRIM(MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255))





Kate
"Ron Rosenfeld" wrote:

On Fri, 17 Feb 2006 10:23:27 -0800, "genkate"
wrote:

I have a list of names; want to split into 2 columns-surnames & everything
else. format is always: john q. public/john public/j. p. public,etc. no
suffixes, nothing is after surname. All I need to do is count to the first
space from the RIGHT. Can Excel do that?

Thanks!
Kate


If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))+1,255)


--ron


--ron