Is there a way to separate FirstName and LastName ?
 

I've got one column that has names in it...
Joe Smith
Wilbur Jones
Hank Williams
etc...
I want to separate the FirstName and LastName into columns.

I'll copy the data to other columns, then...
How do I remove all characters AFTER the first Space (= FirstName)?
How do I remove all characters UP TO and including the first Space (=
LastName)?

I understand that won't work for all names, like...
T S Elliott
but it will work for most.

Thanks in advance, people.

Mark246

Is there a way to separate FirstName and LastName ?
 

You can use
<Data <Text To Columns

Post back with any questions.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Mark246" wrote in message
...
I've got one column that has names in it...
Joe Smith
Wilbur Jones
Hank Williams
etc...
I want to separate the FirstName and LastName into columns.

I'll copy the data to other columns, then...
How do I remove all characters AFTER the first Space (= FirstName)?
How do I remove all characters UP TO and including the first Space (=
LastName)?

I understand that won't work for all names, like...
T S Elliott
but it will work for most.

Thanks in advance, people.

Mark246

Is there a way to separate FirstName and LastName ?
 

You can insert a column to the right of your name column and then use
Data/Text To Columns with space as your delimiter.
If there are spaces in the names you don't want you can use the TRIM
function to remove them. =TRIM(A1) removes all
leading, trailing spaces and reduces repeating imbedded spaces to 1 space.

Tyro

"Mark246" wrote in message
...
I've got one column that has names in it...
Joe Smith
Wilbur Jones
Hank Williams
etc...
I want to separate the FirstName and LastName into columns.

I'll copy the data to other columns, then...
How do I remove all characters AFTER the first Space (= FirstName)?
How do I remove all characters UP TO and including the first Space (=
LastName)?

I understand that won't work for all names, like...
T S Elliott
but it will work for most.

Thanks in advance, people.

Mark246

Is there a way to separate FirstName and LastName ?
 

I should add that you should insert as many columns as you'll need. For
example John Doe will require one extra column as the John will stay in the
original column and Doe will be in the inserted column. T S Elliott will
require two extra columns with T in the original column, S in the first
inserted column and Elliott in the second inserted column.

Tyro

"Tyro" wrote in message
et...
You can insert a column to the right of your name column and then use
Data/Text To Columns with space as your delimiter.
If there are spaces in the names you don't want you can use the TRIM
function to remove them. =TRIM(A1) removes all
leading, trailing spaces and reduces repeating imbedded spaces to 1 space.

Tyro

"Mark246" wrote in message
...
I've got one column that has names in it...
Joe Smith
Wilbur Jones
Hank Williams
etc...
I want to separate the FirstName and LastName into columns.

I'll copy the data to other columns, then...
How do I remove all characters AFTER the first Space (= FirstName)?
How do I remove all characters UP TO and including the first Space (=
LastName)?

I understand that won't work for all names, like...
T S Elliott
but it will work for most.

Thanks in advance, people.

Mark246

Is there a way to separate FirstName and LastName ?
 

BRILLIANT !

I LOVE This forum. You people are terrific.

There were 185,000 records. I just moved to another sheet everything
that went into more than 2 columns. Is OK. I'll deal with them
later.

Next problem...
Many of the 150,000 records are now in the format...
Smith, John
I want them all like...
John Smith
How can I go thru the columns and...
Find any COMMAs in the FirstName column (that is actually a LastName),
Anytime there is a comma, Delete the comma,
Move the JOHN to the Previous column, and
Move the SMITH to Next column.

If it takes several steps... no problem.

Thanks, people.

Mark246