Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Separate First And Second Name In One Cell Into Separate Cells.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

My list has the first and last names in the same cell. How can I separate
these into two separate cells so I can sort by last name. Or is there a way
to sort by the last name when the name in the cell is "firstname lastname"?


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics







Hi-light the column.
Data -> Text to Columns...
Answer the resulting wizard
--
Gary's Student


"Dwight in Georgia" wrote:

> My list has the first and last names in the same cell. How can I separate
> these into two separate cells so I can sort by last name. Or is there a way
> to sort by the last name when the name in the cell is "firstname lastname"?



e.g.
one cell shows
lastname, firstname

and I want to break it out into 2 separate cells with lastname in one and firstname in the other......

any suggestions?

thanks!
jo


The database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column. There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.

Is there any way to get Excel to take the names entered into one cell and split them into three cells?

I've attached a spreadsheet example that shows what I need to do.


My names in my file are inserted in one column as lastname, firstname. I need to separate them and put them in two different columns. "find" doesn't seem to work. My version of excel 2000 just gives me a message that it isn't supported. Any ideas on how to find the ", " and separate the first and last names without using "find"?

I tried: right(clientname, len(clientname))-find(", ",(clientname))

didn't work.


In Column A I have cells with FirstName LastName.
I want to separate the names and have FirstName in Col A and LastName in Col B. Please advise. Thanks!!!


How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname?

Thanks!


How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname?

Thanks!


There has got to be an easy way to do this, but I'm at a loss...

I have a spreadsheet with an enourmous about of names in one column, but they are in each cell "FirstName LastName". I want to sort by the LAST name which is the second word in each cell.

Preferably I'd like a way to mass-change each cell so that I would have "LastName, FirstName"...

...But I would settle for just having a way to sort by the last name without physically changing each cell.

Any ideas?


I've found several similar posts but nothing that will do exactly what I need. I have a list of several names that are in the form of FirstLast or FirstMiddleLast (no spaces, beginning of each name capitalized). I also have a separate file with these same names separated and put into 2 separate columns. I need to separate the FirstLast names into a firstname and lastname string so that I can search for exact matches in the other file with the names already in separate columns which I can do once I get the names separated.

To complicate issues, some first names are initials like J.J., and some people do not have middle names, and some middle names are initials. So I would need to be able to come up with strings for each of the following examples:

JohnHarris
ScottLeeBrown
MikeP.Smith
J.J.Robinson
JamesS.P.Black

that return:

First Last
John Harris
Scott Lee Brown
Mike P. Smith
J.J. Robinson
James S.P. Black

The forum deleted the formatting, but the last name will always just be one name and the first will be everything else.

One way I was thinking that may be easiest (if possible) is to search for the last capital letter and turn the rest of the string following it into the lastname and then take the rest and put it into the first name, then separate the first from the middle name in that string. Thanks in advance for any help.


I have a program that gives information on people in one cell in the following format:
Lastname, firstname-id number, lastname, firstname-id number, etc.
I can't control the number of people that would be in the cell. Each person is comma delimited. Is there some formula or something I can do to separate each person and place them in a separate cell, even if on another sheet? I really need this done, and bad...Please help


Is there a way (macro) which would alphabetize by last name when full name is contained in a single cell, example: Andrew Smith? I know I could either reverse first and last names or I could parse first and last names into separate cells and then sort. But is there a way maintain the name as is--FirstName LastName--and still get Excel to alphabetize by last name?


Hi,

Using Excel 2003 on XP.

I have a column containing names in the format LastName FirstName.

Code:

=TRIM(MID(A1,FIND(" ",A1&" ")+1,1024))


I have been extracting the FirstName to a separate column using the above formula I found in a newsgroup.

The problem I have just encountered is where the LastName is composed of more than one name e.g. van Gogh.

How do I amend this formula so that I can ignore a double-barrelled LastName separated by a space and only extract the FirstName to a new column, please?

I don't wish to use Text-To-Columns.

Many thanks.



Hi -
i have a column A that contains customer names, last and first. They are separated by a comma and a space:

"Last, first"

I would like to separate the first and last names into 2 separate columns. Obviously the # of characters will vary from row to row. Is there a way to separate the two?

Jill




Hi,

please help

a) "[=>]" to be substituted by ":"

b) content of column A (of both sheets 1 and 2 of the attached excel file) to be in separate cells (each word in a separate cell)

c) content of each separate cell then to be united again (without loss of the data)

i tried everything, but sadly hopeless.

how to do it ?

thank you very much in advance !

best regards

I have 3 spreadsheets I combined into one,

They are voters for 3 separate occasions. I would like to filter out how many people have voted 2 times or 3,

How would I do this?

I have sorted the information with 2 columns "LASTNAME" and "FIRSTNAME"

Example:
LASTNAME FIRSTNAME
ABBOTT JANET
ABBOTT STUART
ABBOTT JANET
ABBOTT STUART
ABBOTT JANET
ABBOTT STUART

These first 3 show up 3 times, so I would like to take all the info from row one and two to get all the info for Janet and Stu,

I have about 9k instances to sort
can anyone help?

thanks Teylyn, worked out great!


I have a worksheet with a persons name in a single cell. I need to sort
these and I forget how to separate them to another set of cells to get the
last name. Since some names have 2, 3, 4 or more separate item (ex: Thomas
G. Roberts, CPA) I can figure it out.

Any help appreciated?



Hello all,

I have an Excel list of about 30,000 names and email address'. My problem is that of the 30,000 names about 85 need to be split of. For example of the 30,000 emails, 85 might be xxx@cvs.com. I need to separate the "@cvs.com" names from the list. I was thinking if I can separate everything from the @ symbol on, and then lock the rows so that the correct name will stay with it's associated email, I can then sort the "@ and after" alphabetically.

Any ideas how to go about doing this?

Thanks,

John




So I'm an Excel noob and need, what seems to be, a simple VBA script to do the following:


1) I need to check the length of fields in a column and make sure whatever text is in the field is EXACTLY 10 digits long... if it's more, less or blank then the entire row needs to be removed and all cells shifted up.

2)OPTIONAL (Only need if possible by VBA) - I need to separate a full name into 2 separate columns. So right now I have a single column with a name in it, i.e. FIRSTNAME LASTNAME. I need a script to identify the space between the first and last names and then separate the first and last names. This helps me import leads properly into my ZOHO system. This isn't super important, but would be very useful.


Any help is appreciated guys! I just don't know how to do it ;(

Please advise on how to separate a text string that has no delimiters? The cells contain names without spaces or commas in this format: FirstnameLastname. The result I want is two columns with firstname in one and lastname in the second column. The only thing distinguishing the two desired fields within the string is an upper case letter at the beginning of first and last names.

Thanks!


Hello,

I am trying to separate the contents of a cell into upto 4 other cells depending on contents.

"jlawrence44" "plawrence20" "rash" "sash4" is currently in one cell.

seem to be separated by a space. I want to be able to move into upto 4 cells.

The sheet has 1000 + rows so wanted to be able to do this for all cells. Have not used excel for a while so hence the question. Please can someone help me with this? Thanks Matthew


I have data in 2 worksheets, say WS1, WS2

WS1 contains following plus other data (here i used : to separate cells)
--------------------------------------
FirstName : LastName : UserID
--------------------------------------
Serine S H : Koay : WKOAS
Roger : Groebi : WGROR
Jill : Neubronner : WNEUJ
Su Ann : Low : WLOWV
--------------------------------------

WS2 contains following plus other data (here i used : to separate cells)
--------------------------------------
FirstName : LastName : UserID
--------------------------------------
Serine : Koay :
Roger A : Groebi :
Neubronner : Jill :
Su : Ann Low :
--------------------------------------

I need to find userID in WS2 for corresponding names from WS1. I joined First and Lastnames in both the worksheets, used

VLOOKUP and got the userid in WS2, if the names matches exactly (not shown here). But if the names doesn't match exactly, as

above, VLOOKUP doesn't work. Eventhough we can see the names are there, but either contain additional/less initials,

reversed first and last name etc.

Is there any other way I can get the result?


I have a separate program that generates a report that I open in Excel and for customer name, it returns it as: "Smith, Joe" into a single cell. I want it to read "Joe Smith" instead. I have found a way to separate the first and last names into a separate cell each and then combine back into "Joe Smith", but I was hoping something could be simpler.


Thanks!


I have a 'Summary Totals' worksheet that has a list of 24 employee names as 'lastname, firstname' in column 'A' beginning at A7. There is a separate worksheet in my workbook for each employee. Cell K21 in each employee's worksheet has the total number of days they worked in the month.

Using, as an example for the first three names at A7, A8, and A9
Aguila, Cynthia
Baterman, Jacob
Crompton, Robert

in cell C7, if I enter the call =Aguila!K21 I am returned the value '19' which is the value in cell K21 of the Aguila worksheet (as expected). But I would like to dynamically build the call so I can just use one call in cells C7 through C30 and not have to enter each individual's name in each separate call (because my employee list changes fairly often and I don't want to have to re-write the calls just because the list of employees has changed).

But if (still in C7) I enter:
=CONCATENATE("=",LEFT(A7,SEARCH(",",A7,1)-1),"!","K21")
what I get back is =Aguila!K21 ... not the value at cell K21 of the Aguila worksheet.

How can I make the call I construct in a cell actually execute and return a value?




I have entries from an online form coming into Excel and the name field contains BOTH first and last names. Is there any way for Excel to automatically break the data into two separate cells OR to have it sort the data by last name even though the cell begins with FIRST LAST? Thanks!

Using XL2000:

I have a single page spreadsheet (needed for SubTotal() to work), separated
into 65 sheets via Page Break Preview. The top cell of each page in Col A
contains the student's name (last name, first name). If I add a student,
how can I then sort the pages by the student's last name?

Acceptable alternative: Have each student on their own page with sheets
named Lastname Firstname, sort those, and still be able to Subtotal in
cells on those sheets, then Grand Total on a separate sheet.

--
David