Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LastName, FirstName covert to FirstName LastName | Excel Discussion (Misc queries) | |||
How to change lastname, firstname to firstname lastname in Excel? | Excel Discussion (Misc queries) | |||
Firstname Lastname Field, Split two cells | Excel Worksheet Functions | |||
FirstName, LastName and vice versa | Excel Worksheet Functions | |||
Seperate Cell Information (lastname, firstname) | Excel Discussion (Misc queries) |