Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Seperate Cell Information (lastname, firstname)
I have a spreadsheet with a column that contains names. They are all in this format: -lastname-, -firstname- example: WASHINGTON, GEORGE All the individuals have e-mail addresses based on their names. They a It's not really " but I don't want to mention the company name. How would I set this up in another column? Or, how would I break apart the names so the first names are in one column and the last name is in another? I would then be able to combine them with the @email.com after them. Thanks! -- JFALK ------------------------------------------------------------------------ JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
#2
|
|||
|
|||
Hello,
The answer to your quandry (assuming the name are in column A) ... First Name: =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)+1) Last Name: =LEFT(A1,FIND(",",A1,1)-1) Although you could do this with one formula: =RIGHT(A1,LEN(A1)-FIND(" " HTH -- Regards, Zack Barresse, aka firefytr "JFALK" wrote in message ... I have a spreadsheet with a column that contains names. They are all in this format: -lastname-, -firstname- example: WASHINGTON, GEORGE All the individuals have e-mail addresses based on their names. They a It's not really " but I don't want to mention the company name. How would I set this up in another column? Or, how would I break apart the names so the first names are in one column and the last name is in another? I would then be able to combine them with the @email.com after them. Thanks! -- JFALK ------------------------------------------------------------------------ JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
#3
|
|||
|
|||
Hi JFALK,
Try this formula: =TRIM(LEFT(A1,FIND(",",A1)-1))&"."&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"@ema il.com" Regards, KL "JFALK" wrote in message ... I have a spreadsheet with a column that contains names. They are all in this format: -lastname-, -firstname- example: WASHINGTON, GEORGE All the individuals have e-mail addresses based on their names. They a It's not really " but I don't want to mention the company name. How would I set this up in another column? Or, how would I break apart the names so the first names are in one column and the last name is in another? I would then be able to combine them with the @email.com after them. Thanks! -- JFALK ------------------------------------------------------------------------ JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
#4
|
|||
|
|||
sorry, meant
=TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"."&TRIM(LEF " KL "KL" wrote in message ... Hi JFALK, Try this formula: =TRIM(LEFT(A1,FIND(",",A1)-1))&"."&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"@ema il.com" Regards, KL "JFALK" wrote in message ... I have a spreadsheet with a column that contains names. They are all in this format: -lastname-, -firstname- example: WASHINGTON, GEORGE All the individuals have e-mail addresses based on their names. They a It's not really " but I don't want to mention the company name. How would I set this up in another column? Or, how would I break apart the names so the first names are in one column and the last name is in another? I would then be able to combine them with the @email.com after them. Thanks! -- JFALK ------------------------------------------------------------------------ JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
#5
|
|||
|
|||
Separate the names using Data/Text to Columns. Select Delimited. The
delimiters are a space and a comma (unless there are no spaces between the the comma and the first name). Be sure to select your 'Destination' or else the separated names will replace your highlighted names. |
#6
|
|||
|
|||
If the name Washington, George is in cell A1, to extract the surname into cell b1 enter the following formula =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) to extract the First name into cell c1 enter the following formula =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Hope this helps To re join the names you can use the function concatenate -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
#7
|
|||
|
|||
Actually, shortly after posting this I checked the "Office on the Web" site. They suggested the "Text to Columns..." option under "Data". It worked for seperating them out. Then I just combined them using the &"."& function. Thanks anyways! -- JFALK ------------------------------------------------------------------------ JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728 View this thread: http://www.excelforum.com/showthread...hreadid=393437 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Seperate the data from the Cell | Excel Worksheet Functions | |||
Can you conditional format based on information in cell comments? | Excel Worksheet Functions | |||
how can i automatically copy the information in one cell on a she. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |