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

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!

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!!!

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

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

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,

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

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!

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 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 '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'm entering bank statements with various expense accounts (rent, accounting, legal, bank charges, insurance, other...etc.). I enter each payment in a separate row labeled with the respective account names. (SEE ATTACHED spreadsheets)

I want to automatically sum all the amounts for the different accounts on a separate worksheet, so that I don't have to sort them manually and select cells to sum them. I know I could sort them alphabetically and then sum each account up manually, but this is not optimal for a large number of accounts. Is there a vlookup function or code to do this better?

Attachments:
"Test - sort & sum" <-- this is what I start with
"Test - sort & sum (solution)" <-- this is what i want to end with (see the solution worksheet)

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

Hi all, I love this wesbite, and have used tips in my everyday job. But I need help with trying to separate first & last names using a formula. I followed the tip that said to separate George W Bush, to use: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))). And it worked.

My problem is that I rec'd a huge file, and the names showed up like this: George*W*Bush, so I can't sort the file unless I can pull the last name out.

Can anyone help me? I would really appreciate it.

Thank you, Donna

I am looking for a way to loop through a column of cells and copy the contents (people's names) to another column in a separate workbook. The issue is that some of the cells contain more than one name separated by either commas or &'s. I would like each name to be copied to a separate cell.

So if the cell contains: J Richards, T Harband, G Gangard
I would like the names to be listed as:

J Richards
T Harband
G Gangard

each in a separate cell of the column. I have been experimenting with the Text To Columns feature, but since each cell contains a different number of names, anywhere from 1 to 20, it doesn't seem to be a good solution. Any suggestions?