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


Advertisements


Free Excel Forum

How To Separate First And Last Names Entered In One Cell Into Two Different Cells

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

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.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
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
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

Similar Topics







I have 3 spreadsheets that have lists of students' names on them, hours they've been tutored, etc.. I want to pull out the first and last names (which are in two separate columns) of all kids that have been in all 3 spreadsheets into a separate sheet. Is this kind of thing possible?

Thanks, Robert


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.


Hello All,

I have an excel file that contains names of people. There is one column that contains the first name, middle initial, and the last name all in one cell. On one line there may be the first name, middle initial, and last name. On another line there is just the first name and last name. The names are separated by spaces. They do not run all together. I want to separate the first name and last name into 2 different cells and eliminate the middle initial. I don't think I can do text to columns because the names are kinda staggered due to the middle initial. Is there a way to do this? Any suggestions would be great.

Thanks in advance,

psu0123


I need some help with names.

I have a list of names as follows:

Smith, John A
Jones, Jane D

How do I separate these into columns. I know how to get the middle initial into a separate column, but what I really need is last name in a column, first name in a column and then middle initial. From there I can write a formula to give me the user ID info I need, first initial of the first name, the middle initial and the first four initials of the last name.
I just need help separating the names into columns.

Thanks !!


I want to separate names, example, Doe, John D., that are entered in one cell.
How to separate them into 2 or 3 cells?



Hi, I am new and would be most grateful if someone could tell me how to solve a problem. I have a spreadsheet with approximately 1100 names that I use for a mail merge. I have separate spreadsheet including about 200 of those same names. I don't want the 200 names in the smaller spreadsheet to be included in the mail merge. How can I compare the two and eliminate the 200 names from my master spreadsheet? Thank you, Donna


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.


Hey all,

I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J

Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.

Thanks for any response


I have a list of Full names (John A Doe) and am trying to separate the cells in First, Middle, and Last names. Some do not have middle initials and some have double last names. How do I separate out the names?


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"?



I have two very large Excel files that have literally hundreds of names. The names reside in Column G in both the spreadsheets. I'd like to use a third spreadsheet that has names in it that I choose. Then those names will compare what is in the two original spreadsheets cut out all the names not appearing in the third spreadsheet. This way I can center around the dozen or so names I'm interested in each day. Your help very much appreciated.

Tony


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!


Hey all,

I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J

Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.

Thanks for any respons

Hey all,

I am having some difficulty coming up with an excel formula that splits last name, first name, and middle initials in separate columns. There's variation where the middle initial may be one letter or an entire word and sometimes the character "&" is tossed at the end of column:
CAGLE,DANNY D &
SNEIR,LARRY &
ALONSO,MERRIL M
KIM,JIN SOO &
HARRISON,GEORGE & NANCY
LUKS,BARBARA VACCARO
MILES,JAMIE & MARLON D
LITT,JEFFREY D & LISA J

Is there an excel calculation to get last, first, and middle initial (whether one character or several) in their own columns while getting rid of the "&" unless it's between two first names? So basically the "&" goes away if it's the very last character. Note that if there's two first names, then the "&" would stay and only the initial of righter most name would be split in own column.

Thanks for any response


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,
sorry if this is a newbie question.
I have 2 spreadsheets each having one column of names and several columns of data for each name. Many of the names are the same, but the columns do not match exactly. I am trying to merge the two spreadsheets so that there is one column with all the names from both spreadsheets with the corresponding data for the names. However, I cant figure out how to make excel recognize the identical names and put all the data from both spreadsheets on the same row with that name.

Ex:
spreadsheet 1:
Steve 343 45 23
Paul 32 3 23
Frank 223 23 12

spreadsheet 2:
Tom 33 76 6
Steve 23 34 3
Paul 1 334 45

I would like to combine the data so that all the names are shown, but Steve and Paul are only shown once. There would be six columns of data, so I dont need that combined.

Does anyone know how to do this?
I appreciate your help.


I'm working with names in a single column that appear as shown below

C1 = Robert Jones
C1 = Robert, Jones

I want to change the order so names appear as lastname,first name for both situations. Using the first example, a comma would need to be added once the order is switched.

I searched Google, but it only shows how to split the first and last names into separate columns. I want to keep everything in the same column.

Any suggestions on how to do this? Can I do this with a formula or do I need to write it as VBA?

Thanks,

Tom


I have a problem with seperating text in a cell. I have a database with people in the following format:

LAST NAMES First Names
LASTNAME First Name
LASTNAME Firstname
etc.

The last name of the person is in capitals, from the first name only the first letter is a capital. I want to seperate first and last name into two seprate columns. The problem is (and why i cant use text to columns) is that an individual can have multiple first or last names and I want all last names in a cell and al first names in a cell.

Does anybody have any idea as to how to solve this (preferrably in a formula?)


I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).

For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.

Can someone help me? Thanks!


Ok, I have a spreadsheet that a client gave me that will be used for a Data Merge. It lists Patient Names/Addresses as well as their respective Doctors. Most all of the Doctors listed have multiple Patients.

ie:
Patient Name | Patient Address | Doctor Name | Doctor Address

Mary | NY | Dr. Ross | NY
John | NY | Dr. Ross | NY
Tim | NY | Dr. Ross | NY
Jane | MI | Dr. Moore | MI
Jacob | MI | Dr. Moore | MI


The Patient name/address is in the same row as their Doctor's name/address, so I basically have multiple records of the Doctor for each patient. The merged letter that I need the data for will have the Doctor's name/address at the top of the letter and ALL of their Patient's names/address at the end of the letter.

My question is this: How do I automate Excel to see the Doctor's names and then list all of the Patient names attached to said Doctor without reading the wrong Patient names? (the patient names are listed first by the way)

The letter will be formatted like this:
-------------------------------------------
<Doctor Name>
<Address>

Here is the text of the letter...

<Patient 1>
<Patient 2>
<Patient 3>
-------------------------------------------

I have attached a sample with only a few names. The actual list is about 1000 different names.

Any help with this matter would be greatly appreciated.


In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?





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 ;(

Hi guys

I have a column of user names eg

Joe Bloggs
Adam barry Smith
etc

How can I separate this into just first names and last names (ie ignoring any middle names) I can get the first name using

left(a1,find(" ",a1)-1)

but can't work out how to do the same for the last name

Cheers

Jim


This is where you introduce yourself? I make lists for my gf's dad's company using excel. One of my most excruciating tasks is to separate the first and lasts names from one cell into two cells. Does anyone know a quicker way to do this other than manually? I have 10,000 names to do and it's going to be hours if I do it by hand. Hello and thanks for readin'.


I have names listed in separate columns, but I need them to be in one column and show as Last Name, First Name. I know how to separate names (Text To Columns), but how do you put them together? Is there something that does it, or could a formula do it? Help! I have a huge list of names and I don't want to retype them.