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

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.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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

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:

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

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?

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!

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?


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'm drawing a blank here. I had a few options around this in 2003 but cannot seem to figure it out in 2007.

I have a database that I'd like to import into excel to run some daily reports.

By default Access uses the field name as the column header. I have entered my customer column names in the caption fields for each column. When I perform the query in Access the column headers are named the same as what I have entered into the caption field. When I then import that database into excel it changes back to naming the columns headings with the field name.

I can either have set column headers and import my data without the column names or somehow import the customer names with my data into excel. Anyone have steps on doing either of these?


I have two excel spreadsheets which for audits are logged by two separate departments. If the work is being logged correctly the spreadsheets have the same information on both, but we need a way of checking both documents and highlighting anything that is on one spreadsheet and not the other.

The spreadsheets have columns for Name, reference number, and agent

I am creating an excel file for cell phone bills of multiple company members that covers a 3 month period. We are trying to see how much of each service we provide that our members are actually using. How can I make each column that defines a specific service, split into each of the 3 months we are examining. I do not want to see the members names 3 separate times in the first column.

I have it set up with all my data but my first column is the last names of our members. I do not want to see their names three times in a row because it is broken into rows for each month. I want one name on the left, the services in the columns headings, and then each value for each of the 3 months we are examining.

Hi there,

In column A I have locality names (ranges from 5 to 20 localities) separated by a line feed (Alt Enter). Columns B to G contain a number or word that relates to the group of localities in column A.
I want to split the localities into separate rows and copy the values in columns B to G.
I found a macro at ( that split the localities successfully but it did not carry the other column values to the new rows.

Any help would be greatly appreciated.

Thanks in advance!

I have a customer list of around 6000 names and addresses. I have a separate list of names with email addresses. Is there a way for me to do some sort of lookup function to get the email addresses pulled from the one list and put to the corresponding names on the other list. I need it to check for both first and last names due to the fact that I have a lot of duplicate last names. Thanks in advance for any help on this.

Brian Sells

Please someone help me here, my boss designed this table for training needs of our department. its is a spreadsheet of training courses versus dates and names are inserted into the cells. he also made a legends of all the names in the department.

what he wants is the names to be hidden on the spreadsheet until he click on individual name in the legend, then only that name appears on the spreadsheet against any training selected for that person.

hope you understand this.

i need a solution to this. some one help!!!!

We need to generate all possible name combinations using, which for now
includes 10 first names, 26 middle initials (a-z), and 10 last 2,600 total names- with no dupes..

If we start with the 10 first names in column/field 1 and the 26
initials in field 2 and the 10 last names in field 3, is there a single
formula or series of steps to end up with the 2600 names in a single
field, with 2600 records?

A super bonus would be a space between the first name and middle
intiial, and then a period after middle initial (which, actually, I
assume we could build into/onto the 26 initial items) and then another
space and then last name...ex. "Happy B. Smart" (these are character
names for childrens project). If we set up the intiial 26 letters in
field 2 and the 10 last names in field 3 with a space prior to each of
them, will the formula simply leave the space in place- or is it more



I have seen many different ways to extract middle names and last names. Some are really confusing to figure out.

Some people use, find and others use search to find a pattern occurring.

What is the best and easiest way/formula to use to extract middle and last names. I guess the first name is pretty easy as everyone seems to extract the first name using the same formula. What if there is 1,2 or more middle names...



What is the best method/easiest formula to extract any middle names and last names. Can you please give examples of different formulas.

What's the difference between the search and find?



I have a workbook with 45 sheets and I am looking for a script to save all sheets as separate text files. The script should take the "save as" file names from the sheet names.

Although they should be saved as text files, the extension is php as given in the sheet names.

Unfortunately I've been out of practice for years with Excel VBA macros of this deepth, so I would highly appreciate some help for the above!

Thanks a lot, Minnesaenger

I have several incorrectly formatted files that contain camel case in cells that should be split.
example: LastnameFirstname should be Lastname "tab" Firstname

There is no option for text to columns because there is no delimiter to use for the split.
Does anyone know how to (via formula hopefully, but macro or vb is ok if I can learn how to use it):

----put a space (or other delimiter) before the second and any concurent Capital letters so that the text to columns can be used
----split the names in one swoop

Thanks in advance,

Not sure how the formula should read to swap first name with last name in a
sheet. All names were entered using First name, Middle initial and Last
name. Sometimes Middle name is used rather than initial. Not sure how to
designate position of Last name. Thanks.

Hi guys:

I have a workbook with 16 separate, named worksheets. One of the worksheets references the first 14 others by using a simple list off-screen in the AD column. I have a MATCH formula which uses the names of the worksheets from the list to identify a name and then "scratch" it off on the worksheet that has the named list.

My problem is, I need to change three of the worksheet names. I went ahead and changed the names on the three worksheets and that worked fine. But, everytime I try to go to the worksheet that has the list of worksheet names, after I click it stalls for a second, and then crashes. I reload it and everything and it works fine with the original names, but it just won't let me change the names of the worksheets. This is a big problem for the functionality of my workbook.

Thanks much.

Hi all

Am trying to add various aspects to a chart in Excel2003 and this means I need to hide the series names, and its relevant indicator, from the legend. In effect this means I need to split the legend into its separate 'pieces'.

Is this possible?

Hope soemone can help.

Kind regards


Is it possible to assign values to names in a list, so that when you validate
it as a drop-down list, you can select a name from the drop-down and it's
corresponding value will be added to separate cell? Basically, I have survey
questions which have five possible responses: Excellent - Very Good - Good -
Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very
Good and so on, so that when a response is selected from a drop-down, it's
value appears in a separate cell (so that we can calculate a total and
average score from the selections).


One column of names, antoher column of names.

Some names are in both columns, some are not.

How does one shuffle both of the columns, with identical entries not entered twice, in the column between them both?[/b]

Hi folks, I've got a scenario I need help with. I've got an Excel file containing names, last names in column C and first names in column D. I've got another file containing a different list of names in the same format. What I want to do is check if the full names in file 2 match any of the full names in file 1.

Here's an example:

File 1:

File 2:

I'm looking for a formula that will see if the values in that row of column C and D in file 2 match any rows in column C and D of file 1. So if there's a JOHN DOE in file 1, the formula will return "MATCH" or some confirmation that the names are in both lists, and return blank if the full name isn't in file 1. I tried all kinds of combinations of VLOOKUP, EXACT, FIND, MATCH, AND, IF, and ISNA, but the best I've come up with is two separate lookups and a check if both matched. The problem with that is that if I'm looking for JOHN DOE and there's someone with the last name of DOE and the first name of JOHN, the check will return true even if the matches aren't in adjacent cels in the same row. Any help would be greatly appreciated. Thanks!

Hello All:

I have a spreadsheet with peoples' names. They are separated by Last name, first name and middle name.

Some of the middle names have more than one character.

I wish to place a "." next the a middle initial ONLY if the middle name contains only one character. If the middle name contains more than one character, it must be ignored.

Is there a formula I can use to accomplish this?

Thank you all very much


How would I create a list of all data in Column A that has specific matching data next to it in Column B? For example in the attached image, I have 6 names in Column A. Adjacent cells in Column B have either the Letter M or F. In Column D I manually entered all the names with an M to their right, but I would like some sort of fucntion to do it for me instead.

Hi everyone,

I am an excel beginner and this is my first time posting on this site.
I have no experience with macros, and am only just starting to learn
the ropes of other basic functions.

Currently I have multiple worksheets set up. I want to create a drop
down box in a worksheet that has 5 car dealership names. From this
drop down box i want to be able to click on each one of these names and

have each one be a separate link to a different spot on a different


I have a worksheet called "Brand Names". On this worksheet i want to
create a drop down box that has the name of 5 car dealerships. When I
click on one of these dealerships (say markville chevrolet), i want it
to take me to the section Markville Chevrolet I have created in a
separate worksheet, "Individual Dealer". I need all of the links to go

to this individual dealer sheet, but land at different cell references
within the sheet.

Any help would be greatly appreciated,


I have a pretty basic spreadsheet set up to calculate payroll for some of our temporary workers. I have a table of workers names and their pay rates in the lower right hand side of my worksheet.

Up until now I have been glancing over to determining their pay rate and manually entering that rate into a specific cell (I2) to start the pay calculations. What I would like to do is somehow add another column to the left of their names and place an "x" or something in that column for the specific employee I am calculating. Once the "x" is placed there I would conditionally format the employees name to be highlighted and have their wage entered into cell I2.

So what I need is a formula to enter into cell I2 which searches cells K5:K24 and when it finds an "x" it enters the payrol rate from the corresponding cell in the range of N5:N24

I know I could revamp my spreadsheet and make a drop down list of names and when a name is selected do a vlookup for the pay rate, but I am looking for a different and hopefully simpler solution.

Thanks, Spence