Edit/Replace to seperate text in columns
 

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

Edit/Replace to seperate text in columns
 

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 EditCopy
Then, EditPaste SpecialValuesOK

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

Biff

"Donna" wrote in message
...
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

Edit/Replace to seperate text in columns
 

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

"T. Valko" wrote:

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 EditCopy
Then, EditPaste SpecialValuesOK

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

Biff

"Donna" wrote in message
...
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

Edit/Replace to seperate text in columns
 

You're welcome. Thanks for the feedback!

Biff

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

"T. Valko" wrote:

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 EditCopy
Then, EditPaste SpecialValuesOK

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

Biff

"Donna" wrote in message
...
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