On Thu, 30 Oct 2008 06:42:10 -0700, Jeremy
wrote:

I need help splitting names out of a cell into two. The problem I am having
is there are some cells with the first middle initial and last name where
some have just the first and last. Below is an example of what I have and
what I want to do.

Currently
A

1 SCOTT BRANDON
2 NEWMAN J ROBERT
3 HENDRIC A ALLAN
4 HENDRIC ALLAN



What I want
A B
1 SCOTT BRANDON
2 NEWMAN J ROBERT
3 HENDRIC A ALLAN
4 HENDRIC ALLAN


Here's one method. It involves extracting the Last Name (last word in the
string), and then removing that Last Name from the original.

B1: =TRIM(SUBSTITUTE(A1,C1,"",1))
C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Note that B1 will display the full name until you enter the formula into C1.

Fill down as required.

If all looks well, you can
Select B1:Cn
Edit/Copy
Select A1
Paste Special Values
which will replace the contents of column A as you request.
--ron