Edit/Replace to seperate text in columns

[Guest]

I currently have

John A Doe Sr.

I want John A in Column A and Doe in Column B. I have attempted to do the
split by:
Duplicating the entire full name in two columns. In the first column I
edit/replace by using the <space>* command replacing with a blank and the
first name is what is left. However, it does not allow me to
<space>*<space>* to gather the middle initial as well.

What else can I do in order to have the First Name and Middle initial in
Column A and Last Name and possible suffix in Column B

 

[T. Valko]

You can split it out into 2 columns using simple formulas, then, after
you're done you can convert the formulas to constants and delete the
original data (after you inspect it and make sure everything looks OK!).

A1 = John A Doe Sr.

B1 = formula:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

C1 = formula:

=TRIM(SUBSTITUTE(A1,B1,""))

Select both B1 and C1 and copy down as needed.

To convert to constants:

Select the range of formulas
Goto Edit>Copy
Then, Edit>Paste Special>Values>OK

Inspect the data. If it's OK you can delete the original.

Biff

 

[Guest]

Thank you, its been a few years since heavily using Excel and I lost some of
the tricks.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thank you, its been a few years since heavily using Excel and I lost some
of
the tricks.