Seperate Cell Information (lastname, firstname)

  • Thread starter Thread starter JFALK
  • Start date Start date
J

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:

(e-mail address removed)
(e-mail address removed)

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!
 
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
 
Hi JFALK,

Try this formula:

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

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

KL
 
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
 
Actually, shortly after posting this I checked the "Office on the Web
site. They suggested the "Text to Columns..." option under "Data". I
worked for seperating them out. Then I just combined them using th
&"."& function. Thanks anyways
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting a message box program to run (Page 43 of 'BeginningProgramming with VBA' by Duane Birnham) 11
Is there a way to separate FirstName and LastName ? 4
Need help setting up weekly mail-merge format 1
Pesky spaces 1
Identify Duplicates that are not identical 4
Converting to FirstName LastName 3
Show only rows with duplicate values in a column? 4
How do I combine two spreadsheets and delete the duplicated info 1

Back
Top