Splitting data in multiple cells

[Jeremy]

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

 

[Ron Rosenfeld]

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

 

[Jeremy]

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

This didn't work. It gave me zeros

 

[Ron Rosenfeld]

This didn't work. It gave me zeros

Before or after you went through the copy | paste special values routine?

Did you copy and then paste the formulas into the appropriate cells? Or did
you possibly have a typo when you tried to "type" them into the target cell?

If both formulas are returning zeros, and you copy/pasted them correctly into
your worksheet, then your source cells contain zeros.

If your source cells were empty, the formulas return a null string.

Provide some more data, please.
--ron

 

[Jeremy]

I see what you are having me do now. The problem with this is that I can not
have column a in my sheet now when this is done. If a is deleted the data in
b and c will go away. This has to be split in two rows and not have a source
they are pulling from. Text to column would be nice if it left the middle
name or initial with the first and only moved the last name to b.

 

[Ron Rosenfeld]

I see what you are having me do now. The problem with this is that I can not
have column a in my sheet now when this is done. If a is deleted the data in
b and c will go away. This has to be split in two rows and not have a source
they are pulling from. Text to column would be nice if it left the middle
name or initial with the first and only moved the last name to b.

I took that issue into account in my response.

Probably you did not follow my instructions the way I expected.

Please post the exact steps you took so we can see what the problem is.

--ron