Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

Easy Way to Manage Names in Excel
How to manage Names in Excel so they are easy to view and change as needed. This is a little trick that I use to k ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell which you have given a cus ...
List All Defined Names and Values in the Worksheet in Excel
Quickly list all Defined Names and their Values in the worksheet without using a macro. This allows you to view an ...
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
In Excel you can store values in Defined Names.  Often people use a Defined Name to refer to a cell on a worksheet ...

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



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


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!


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


I'm using the following formula to change the name format in a column from firstname, lastname to lastname, firstname.

=trim(mid(c1&", "&c1,find(",",substitute(c1," ",","))+1,len(c1)+1))

when I orginally posted my thread, I did not take into account that some of the names contain a middle initial or middle name. When the above formula is run, names can display in one of three formats

A.Jones,Robert

OR

A Jones,Robert

OR

Andy Jones, Robert

How do I modify the formula so it removes a middle initial or middle name and only displays lastname, firstname?

Thanks,

Tom


I have a database that I created with First Name, Initial/Middle Name, and Last Name (all in one cell).

I am now trying to update this database, but the new records I am trying to insert are only First Name, Last Name (all in one cell).

I want to change my existing database to only have First Name, Last Name in the same cell. Also, I want to cross reference my new cells with the existing database in order to ensure I have not duplicated by having an initial in one of the cells.

I have basic knowledge of macros and formatting etc and have played around with several potential solutions, but I'm not having much luck. I managed to separate the existing database cells, so now I have one column which contains either just the first name, or first name last name, depending on whether the person had a middle initial entered.

Please help!


Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles




Hello,

I have a database that has a single column of employee names that I would like to turn into two separate colums--one for first names, one for last names.

Is there an equation or operation that will allow me to do that easily?

Thank you,

Alex




I have a column that needs to be separated into two columns for First and Last name.
Some of the names have one or two middle names and some have Jr. or III at the end.
I need to isolate and separate the first and last name.

Example of what is in cell:
A Clark Andrews
A J J. Langley
Dean Macbeth Jr.
A.J. F Fallico II

Desired output:
A Andrews
A Langley
Dean Macbeth
A.J. Fallico

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!


Hello there people, I have a bit of a problem with searching two separate workbooks.

I am trying to match the data from column C in each workbook. Column C contains the forenames of clients but the middle names are randomly included so when I do a VLOOKUP some cases return back as False even though they match.

I would like to find a new way of doing this so that only the forename is included in the match and any middle names are not included. Hopefully without separating the forenames into other columns. Thus returning more matches and saving me time.

It might be an idea just to VLOOKUP the first letter (initial) of column C instead. Then I can look at the results and select the cases that are duplicates.

Thanks.


So I have a set of about 180 names listed in the following manner:
Last name, first name middle initial. We are not allowed to separate the parts into different columns but rather we have to set up three columns that will extract the three different categories. Say the name is Johnson, Alan A, we would have to extract each part into three different columns. However I have been having a hard time extracting the first name. I have found all of the middle initals and those without one have displayed not having one. However when I do a count of how many people have middle names all of them come out in the data even if it theres a black space. Any help would be greatfully appreciated. thank you.


I have no clue how to do this. I am trying to orginize a spreadsheet that has a list of peoples name and addresses. My problems is all of the names are entered in in first last order, and one name in each cell, so John Adam Doe is one cell. What I would like to be able to do is split the name into two cells, Doe Adam in the first cell, and John in the second. Since all of the names are different lenghts and some have middle names and others don't I am stumped on how to do it, other than doing it all manually, any ideas.