| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
last name, first name strings
 Hi there,I am a newbie and am having some trouble with seperating data in Excel. Here is my issue: I have a table of employee names are in one column alone, but all in a different format: first name -space- last name last name, first name last name -space- first name What I am needing to do is get all the names to be in the same order and seperated into two columns as: Last Name column First Name column How do I take those combined name formats that are in one column and seperate them into seperate columns in the correct order? Thanks, Christina -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
Hi!
Consider this: Marion Ross Which is the first name and which is the last? Biff "data_diva" wrote in message ... Hi there,I am a newbie and am having some trouble with seperating data in Excel. Here is my issue: I have a table of employee names are in one column alone, but all in a different format: first name -space- last name last name, first name last name -space- first name What I am needing to do is get all the names to be in the same order and seperated into two columns as: Last Name column First Name column How do I take those combined name formats that are in one column and seperate them into seperate columns in the correct order? Thanks, Christina -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
I can check the names in a system and pretty much am familiar with the employee names, its just that they are in all formats in one column and I need them all to go the same way. -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
Introduce one extra column in which you manually put the name type: 1, 2 or
3 So column A is the name, column B is the type. Column C: =CHOOSE(B1,RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEFT(A1,FIND(",",A1)-1),LEFT(A1,FIND(" ",A1))) Column D: =CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1))) Copy both down as far as needed -- Kind regards, Niek Otten "data_diva" wrote in message ... I can check the names in a system and pretty much am familiar with the employee names, its just that they are in all formats in one column and I need them all to go the same way. -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
data_diva wrote: I can check the names in a system and pretty much am familiar with the employee names, its just that they are in all formats in one column and I need them all to go the same way. Do like I do and use the K.I.S.S. method. (Keep It Simple Stupid) Bite the bullet: Insert two columns then manually enter each name in the same format. You then can delete the mixed up column to eliminate the confusion. I just wonder why thed names were not all entered in the same format. Ralphael, the OLD one |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
Hi!
That's a pretty good effort but still fails under some fairly common situations: Mary Beth Smith T. Boone Pickens Juan Carlos De Santos =CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1))) Think the references to E1 should be to C1. Not meaning to be a "jerk", but sometimes things that appear to be fairly easy and straightforward at face value aren't so easy after all. I'm sure you know that! Parsing names is still "easier" than parsing mailing addresses! Biff "Niek Otten" wrote in message ... Introduce one extra column in which you manually put the name type: 1, 2 or 3 So column A is the name, column B is the type. Column C: =CHOOSE(B1,RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEFT(A1,FIND(",",A1)-1),LEFT(A1,FIND(" ",A1))) Column D: =CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1))) Copy both down as far as needed -- Kind regards, Niek Otten "data_diva" wrote in message ... I can check the names in a system and pretty much am familiar with the employee names, its just that they are in all formats in one column and I need them all to go the same way. -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
last name, first name strings
<but sometimes things that appear to be fairly easy and straightforward at
face value aren't so easy after all. I'm sure you know that! I sure do. I just followed your instructions and tested the formulas for them I'm sure with the help so far you can figure out whatever variations you may have! If not, don't hesitate to post again in this same thread, with full specifications please -- Kind regards, Niek Otten "Biff" wrote in message ... Hi! That's a pretty good effort but still fails under some fairly common situations: Mary Beth Smith T. Boone Pickens Juan Carlos De Santos =CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1))) Think the references to E1 should be to C1. Not meaning to be a "jerk", but sometimes things that appear to be fairly easy and straightforward at face value aren't so easy after all. I'm sure you know that! Parsing names is still "easier" than parsing mailing addresses! Biff "Niek Otten" wrote in message ... Introduce one extra column in which you manually put the name type: 1, 2 or 3 So column A is the name, column B is the type. Column C: =CHOOSE(B1,RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEFT(A1,FIND(",",A1)-1),LEFT(A1,FIND(" ",A1))) Column D: =CHOOSE(B1,LEFT(A1,LEN(A1)-LEN(E1)),RIGHT(A1,LEN(A1)-LEN(E1)-1),RIGHT(A1,LEN(A1)-LEN(E1))) Copy both down as far as needed -- Kind regards, Niek Otten "data_diva" wrote in message ... I can check the names in a system and pretty much am familiar with the employee names, its just that they are in all formats in one column and I need them all to go the same way. -- data_diva ------------------------------------------------------------------------ data_diva's Profile: http://www.excelforum.com/member.php...o&userid=28885 View this thread: http://www.excelforum.com/showthread...hreadid=486339 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Colomn of strings, how to count each string with "abc" OR "def" | Excel Worksheet Functions | |||
| How can I count strings within strings | Excel Worksheet Functions | |||
| Wildcard MATCH() breaks on long (?) strings | Excel Worksheet Functions | |||
| Compare cells/columns and highlight matching text strings | Excel Worksheet Functions | |||
| Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
Linear Mode
