Seperate Cell Information (lastname, firstname)

[JFALK]

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 are:

firstname.lastname@email.com
[email protected]

It's not really "@email.com" 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!

[Paul Sheppard]

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

[JFALK]

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!

[Zack Barresse]

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("
",A1,1)+1)&"."&LEFT(A1,FIND(",",A1,1)-1)&"@email.com"

HTH

--
Regards,
Zack Barresse, aka firefytr


"JFALK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> are:
>
> [email protected]
> [email protected]
>
> It's not really "@email.com" 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
>

[KL]

Hi JFALK,

Try this formula:

=TRIM(LEFT(A1,FIND(",",A1)-1))&"."&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"@email.com"

Regards,
KL


"JFALK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> are:
>
> [email protected]
> [email protected]
>
> It's not really "@email.com" 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
>

[drlogarithm]

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.

[KL]

sorry, meant

=TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"."&TRIM(LEFT(A1,FIND(",",A1)-1))&"@email.com"

KL


"KL" <[email protected]> wrote in message
news:[email protected]...
> Hi JFALK,
>
> Try this formula:
>
> =TRIM(LEFT(A1,FIND(",",A1)-1))&"."&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))&"@email.com"
>
> Regards,
> KL
>
>
> "JFALK" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>> are:
>>
>> [email protected]
>> [email protected]
>>
>> It's not really "@email.com" 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
>>
>
>