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


Free Excel Forum

Separate Names Into 2 Columns?

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

I have a column of names (first & last name). Is it possible to separate them into two columns - 1 column for first name, next column for last name?


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

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
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
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

Similar Topics

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?


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.

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.

Hello all,

I have a column full of people containing both their first and last names, and I'm hoping there's an easy way to separate that into two columns. There is not a comma or any other punctuation in between the two names (just a space), so the delimiter option won't work, and since the first names are of many different lengths, I can't really use a fixed width, either.

Is there any easy way to separate them?

Thanks all,


I have a couple of columns that look like this in basic form:

Column A Column B
A 1
B 1
C 0
D 2
E 1
F 0
G 2

What I want is in a separate worksheet for it to pull the names (A, B, E) that have 1s next to them in column B and put them in a list.

I could do this kinda manually, but how can I create a nice list in a separate column on a separate worksheet just of the names (column A values) where there is a 1 in column B? All I can think to do is vlookup the data and put blanks where its not equal to 1 and then manually delete out the other rows.

Thanks a lot for the help

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

Hi guys

I have a column of user names eg

Joe Bloggs
Adam barry Smith

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



Hi Guys

Here is my problem.

I have a set of names in one column, another set of names in another column, and another set of names in a third column.

How can I sort so it matches the names in the rows?

Look at this screenshot.

Any help will be much appreciated.

I have a column, A1:A100, that has a bunch of names listed in it. Every so often between some of the names there is the word, Next. I would like to have a formula or macro that is able to take the names and put them into their own separate columns, B1, C1... Whenever there is the word, Next, I would like the names that are after that word to be placed into its own column and so on until the end of the list.

Depending on the day there could be 50 names in one column and 10 in another so the word Next can move around to different cells.

Please ask any questions if I did not explain myself clearly. Thanks.

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?

I am tracking numbers tied to specific names so I have a column of names and a column of related numbers each day, but not every name has a number every day so the name list is not consistent(the names are not always in the same rows since not every name appears each day) and also there are duplicate names (never more than 2 though) I have to track values for separately, so counting the instances and displaying the corresponding value for the second instance is also necessary.

I want to be able to paste the two columns into sheet three each day, then on the msn worksheet use sumif (or something else) to find if the new day's name list contains the names from the name list on the separate worksheet and enter the corresponding numbers by just dragging the colum of formulas over each day.

I just cannot figure out how to expand the reference on the separate sheet without having to manually change the column reference letters (see C18:F18 on MSN worksheet) to account for the alternating colums along with accounting for the duplicate names. I posted an example; any help would be greatly appreciated.



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,


Hello everyone!

I have about 4,000 names and they are all in one column, but in groups of 3's. Example...


... I'd like to separate them into three different columns but they are all different lengths with no common delimiter. Even the spaces between them aren't even. To get the names into columns E, F, and G, I used the LEFT function to get the first name. I used =MID(A1,LEN(E1),LEN(A1)-(LEN(G1)+LEN(E1))) to get the second name, but that will only work if I can figure out a way to get the third name into cell G.

Any clues??? Thanks in advance!

I got huge database with names and last names
Names and Last names are in different rows ..

A1 Name
A2 Last name

A4 Name
A5 Last name


Now i need to make new sheet where i will have two COLUMNS...column A (names)....colums B (Last names)

How to make that without making it one by one separated and pasted into these columns?
Is there any "formula" that can make it for me?


I have a list of names that I need to separate into FIRST and LAST names. These names are located in column A. The problem with my initial list is that some of the names have two first names (in the format of Herman & Betty Altenfelder) and some just one first name (such as Tom Frank). Could someone please help me in writing a function that will place the first name(s) in one column (such as column B) and the last name in a different column (such as column C).

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:


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.

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.


How do you take a whole name and separate it into two separate columns? Example: Column B lists names such as "Mr. John Smith" and I would like to separate the "Mr." into one column, the "John" into another column and "Smith" into another column.

THank you!

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

Here's a fun one:

I've been handed a big excel file that has a lot of names, addresses, city, state, etc. Unfortunately the person who entered all the data neglected to break first and last names into separate columns. Is there some way to do a search and cut, say, looking for the space between the names, to move

David Thomas


David Thomas

Any ideas?

Thanks again folks,

Darrell Leland

Hi All,

I'm pretty new to VBA so please bear with me...

I have a workbook called Lookup.xls which has a master list of names in column A and either the words 'Accept' or 'Decline' as a status next to each name in column B. On a separate workbook (which is generated each week) is a list of names which match column A in Lookup.xls and some corresponding data in other columns. What I need to do is split the list of names into separate sheets by moving all the rows which have a name that matches 'Accept' into a sheet called 'Accept_Sheet'. The ones that are left of course will be 'Decline'.

I've tried various code on the web but nothing that fits exactly what I'm after (and I'm not proficient enough to adapt it yet unfortunately!). Basically, I need to match the name and then move the row based on wether its got Accept in column B of Lookup.xls.

I hope that makes sense,

Many thanks for any help you can give!

Hiya - I have a column in that contains a 9 digit number that I need to separate into 3 separate columns with 3 numbers each.

COLUMN A = 000000000

I need three columns to look like:

Column B = 000
Column C = 000
Column D = 000

Can this be done thru the query screen and if so, can someone please help with me the formula to do this?

Much obliged.

Hi everybody,

I need to solve this problem:

I have 4 columns.
In first are names of employee.
In second are status of employee attendance: arrival, left, lunch, doctor, vacancies etc.
In 3rd column is date and in 4th column is time.

I need to separate each enmployee with his status, dates and time to one sheet (4 employes = 4 sheets). Then separate date and time of status from one column to another columns base on type (arrival, left, lunch...)

Thanks lot.


One of our systems spit out a report of first and last names. Instead of spitting out the report into 2 separate columns, it combined the first and last names into one column and separated the names with a dash (-) (i.e. "John-Smith" as one cell).

Typically I would use text-to-columns, but since the length of the names vary so much (1100+ total line items), I'm not sure that's possible. Is there a formula that can be used that searches for the '-' within the cell and tells it to split it at that point?

Any help would be greatly appreciated!


Hi everyone,

I am trying to separate text in on cell into different columns. The issue is that the text strings are of different length (might consists of four words, two words, one word or more words). In the current file they are listed on top of each other. In the column next to them there is a numerical code that corresponds to the entries in the first column. If there are three names and three numerical codes in column one and two I would like to separate them in a way that each name and numerical code is in a separate column (this would make a total of six new columns). I have attached a sample of the data to get a better understanding (the first row shows how the data should look at the end ).

Thanks a lot for any comments!

P.s. The text to column does not work as the text string have different lengths.