Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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





  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LastName, FirstName covert to FirstName LastName Craig860 Excel Discussion (Misc queries) 4 January 14th 08 10:58 PM
How to change lastname, firstname to firstname lastname in Excel? mert Excel Discussion (Misc queries) 1 January 9th 08 07:06 PM
Firstname Lastname Field, Split two cells Anthony Smith Excel Worksheet Functions 6 February 24th 07 04:37 PM
FirstName, LastName and vice versa Bob Excel Worksheet Functions 3 October 27th 06 06:54 PM
Seperate Cell Information (lastname, firstname) JFALK Excel Discussion (Misc queries) 6 August 5th 05 09:41 PM


All times are GMT +1. The time now is 10:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"