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

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

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

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.

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

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

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