Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

Adding Commas To List Of Names

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

How do I add a comma to a list of names all in one column? The full name is in one cell.

Smith John
Jones Davy
Doe John

View Answers     

Similar Excel Tutorials

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 ...
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 ...
MODE() - Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
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 ...

Helpful Excel Macros

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
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This
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
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
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


im sure this question has been asked many times and its a really simple answer, but ive searched and my brain is going to explode in a minute

ive got a large list of Names in colum C of a worksheet called 'Raw Data'.
they are in alphabetical order.

All i want to do is return 1 instance of each name into another list.


original List:

John smith
John smith
John smith
John smith
John smith
Kenneth Smith
Kenneth Smith
Kenneth Smith
Kenneth Smith
Luke Jones
Luke Jones
Luke Jones
Luke Jones

New List:

John Smith
Kenneth Smith
Luke Jones

any help greatly appreiciated.

version: excel 2003.

This is difficult to explain but I'm trying to write a formula that will look at a cell and see if it contains text that would match one of multiple names from a list. Below is an image showing the basic premise.

The description column contains the string(s) of text where I want to "search" and see if it contains any of the names in the "Last Name" column. If it does, then I would like to populate the "Last, First" column with the corresponding "Full Name". Hope this makes sense. Thank you.

Description Last, First Amount Last Name Full Name 898734 Smith John 150 Smith Smith, John 987233 Jones James 200 Jones Jones, James Johnson Jane 125 Johnson Johnson, Jane John Smith 75 Gonzales Gonzales, Mary 782394 Gonzales Mary 175

Here's my dilemna:

I have a list of full names of people. I want just their last names to be in a cell all by themselves

For example: "John A Smith" would be in one cell, but i want just "Smith" in another cell.

I tried the 'text to columns' approach using the space delimiter approach and that would've worked except for 2 problems... some names begin with a "Mr." or "Mrs." and some names would not have a middle initial.

So for example, you could have "Mr. John A Smith" or "John Smith" or "Mr. John Smith", etc. etc.

My solution would be to have some function to pick just the very last word in the cell. Is there something like that available? Or something similar? Thanks in advance!

I know there is a way to do this somehow, because I did a similar project years ago, but now I'm stumped. I have a list of names in their own cells, such as...

John Smith
Steve Jones
Todd Johnson

I need to reverse those names, in other words, last name first, so it comes out....

Smith, John
Jones, Steve
Johnson, Todd

I may be mistaken, but I seem to remember having to do something like spliting the first and last names into their own cells then recombining, but that may just be my imagination.

Any help out there?


Hi everyone, i have a list of shorthand names (list a), along with a list of full names (list b).

The number of names may not match exactly, but i want to turn all the shorthand names into the full names provided a certain number of letters from names in list (a) match list (b)

So for example, list (a) - John W D, list (b) - John William Doe
turn John W D into John William Doe since J, O, H (or O, H, N etc) match up and are in sequence. What kind of function would i be looking to use?

This is definitely not a foolproof way of doing this, so i was wondering if you have any suggestions? thanks in advance!

My cell contains a list of names, separated by commas. Multiple rows contain the same names. I'd like to pull the names out in order and eliminate all of the rest. All of the names are in the same cell and there are other cells in the row.
For example
Cell 1 Cell 2 Cell 3
Row 1: Smith J, Jones K, Johnson L, Brown P 7 yes
Row 2: Smith J, Jones K, Johnson L, Brown P 5 no
Row 3: Smith J, Jones K, Johnson L, Brown P 1 no
Row 4: Smith J, Jones K, Johnson L, Brown P 4 yes

I'd like to convert to
Cell 1 Cell 2 Cell 3
Row 1: Smith J 7 yes
Row 2: Jones K 5 no
Row 3: Johnson L 1 no
Row 4: Brown P 4 yes

Is there a way to do that?
Thanks in advance for your help.

I have a list of about 400 author names that are First Name Last Name and I want to change them to Last Name, First Name. (ie. John D. Smith to Smith, John D). The full names of the authors are in one column (unfortunately).

Is there a way in excel to do this? I'm hoping I don't have to change them all manually.

Hi gang. Got a list of names I'd like to split out the last name using VBA not a cell formula. Could be such as:
John Smith
John Smith Jr
Mary and Bill Jones
Ed & Edna Brown
Irene Cole-Dunn

So I'm looking for that space before last name to split it into two columns. And that "Jr" is a distinct possibility, as much trouble as it might be.

el mago

I have a phone list where the names were entered with First Name and Last Name with a space in between. How would I change it to have last name then a comma and first name in the same cell?


Current: John Smith
Revised: Smith, John

Thanks for your help!

Hi everyone. I have a giant list people, and am having a problem with cleaning the data and making it uniform. The issue is that there is supposed to be a column that has name suffixes (Jr, Sr, iii ect), but many of the names have the suffix after the last name. I am trying to move them over to the correct coulmn. I have figured out a way of moving over whatever comes after the first space (By having a column counting the distance to the first space, and another the legth of the whole cell, then one to the right and one to the left of that space) The problem is that many people have two last names, and sometimes there is a space between them. Anyone have any ideas? This is about what the data looks like. Any help is appreciated.

Last Name First Name Middle Name Suffix Smith John W Jr
Smith Jr
Jones -Smith

Jones -Smith Jr


I have to copy names (one by one) from a Microsoft Outlook Address Book to an Excel Spredsheet. The names which I want to copy and paste are in the format, Last name, First Name e.g. Smith, John.

Once I copy & paste the names into Excel, what formula can I use to change them to First Name then Last Name e.g. John Smith I need the comma to be deleted also.

Also, is there a formula which will change ALL the names for me, not just the first one without an error?

If anyone could please help it would be great! I'm going to have hundreds of names = S


Hi all,

I have a task of copying lots of names to a worksheet, and the problem is they are coming accross in reverse order. eg. Smith John instead of John Smith. I have found a formula which does this, however some names have (a) on the end of them eg. Smith John (a) and sometimes without a space eg Smith John(a).

What I need is for the names to be reversed and have the (a) deleted if it occurs.

Can anyone help?



Say for instance I have the following:

John Smith (2 names)
John Joe Smith (3 names)
John Joe Ryan Smith (4 names)
Mr. John Joe Ryan Smith (title & 4 names)

Is there a formula that could be universally applied across all of the above forms of names that would only spit out the last name, "Smith"?

Hi folks,

First post so hope this makes sense may not be possible - sorry if not!

I'm trying to conditionally format a text box of staff members name against a list of staff leave. Basically I want to write a rota of names in column A and then if it conflicts with the names in colum B turn the background red for example. this is laid out as:

Column A Column B
John Smith John Smith Jane Doe Alan Jones

Currently all of the names are in one cell - I guess I could split these out by using text to columns to make one name per column if that helped?

Any advice greatfully received!


Is there any way I can manipulate the vlookup function so that it can identify the difference between say, 'J Smith' and 'John Smith'?

I have two worksheets - Sheet 1 has column A with a list of names with just the initial and surname while Sheet 2 has column A with the exact same list of names, however the full first name is given along with the surname.

I have tried using the 'TRUE' function at the end of the vlookup to return an approximate match but it doesn't return the correct value (it returns 0 or #N/A). I know that it is looking in the right place as if I change the full name to just the initial and use the 'FALSE' function, it returns the right value.

So basically I am asking:

1. Is there any other command or function i can use with vlookup so that it knows that when it is looking for J Smith, it returns the required value for John Smith?

2. If the above is not possible is there any sort of macro or command that will replace the full first names and surname in column A with just the initial and surname?

Many thanks

I created a list of names in a column:

John Smith
Jane Doe
John Doe

Is there a way to flip flop them to change the cell so that the last name is first:

Smith, John
Doe, Jane
Doe, John


I am attempting to compare 6 columns of data (names). For instance:

Column 1..............Column 2................Column 3
Jones, Amanda......Davis, Joe...............Davis, Joe
Smith, John...........Jones, Amanda........Evans, Mike
Turner, Tina..........Smith, John.............Jones, Amanda

I need to do a comparison to find duplicates and I need to know how many times a particular name appears on this worksheet. If it is possible create new columns with this information, this would be very helpful. For instance, the results I am looking for would be something like:

Davis, Joe 2
Jones, Amanda 3
Smith, John 2

I appreciate any help that can be offered. I would probably be considered a novice in excel.

I am working in Excel 2007 on Windows XP OS and am not too knowledgable with creating a formula for switching first names and last names in a table.

column A currently has:
John Smith

I want change it to last name first, a comma immediately following the last name, space, and finally the first name:
Smith, John

Does anyone know a formula for the cell so I can drag it down for all 20,000 entries? It would be very much appreciated.



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!

Hi all,

Just wondering how to search through a list of last names in column B, then find all of the last names which are the same as what is being searched for, then list them along with their first name which is located in the adjacent column A, this list of names is then displayed to the user to chose the name needed.

a) User enters in the last name "Smith".
b) program searches all of column B and finds 3 occurences of the last name "Smith".
c) it then adds the first name to each of them which is located in column A next to each "Smith".
ie. John Smith
Larry Smith
Fred Smith
Maybe list these in a list box or something.
d) Now the user wants Larry Smith, so all he does is click on Larry Smith, and then that full name is entered into column A on the next worksheet.

Hope someone can help me with this, or steer me in a direction of how to achieve this. I can currently find the first occurrence, and then add the first name, but that's it.



I have two lists with, both with the same named/numbered columns. Each has a list of names in the first "name of" column, but different values in two of the other columns, though some names are shared between the two lists (CSV format). I want to combine the two lists, but combining the values of the other two columns, placing them in the correct row.


(List one)
Column A Column B Column C Column D
John Smith yes - -
Bob Smith - yes -
Joe Smith - yes -

(List one)
Column A Column B Column C Column D
John Smith - yes -
Jill - - yes
Bob Smith yes - -

Combined...what I want
Column A Column B Column C Column D
John Smith yes yes -
Jill - - yes
Bob Smith yes yes -

Does this make sense? I basically want to combine the files, and retain the column values, without duplicating the rows.


I have a question not sure if I am using Vlookup correctly but hopefully someone can help.

In sheet 1 I have a list of names listed like this: John Smith
In Sheet 2 I have a list of names that I exported from a mail client that are listed like such: Smith, John,,"","",06/04/2008,""
Because sheet 2 has like three thousand names and I only need to extract about of 200 of them which are listed in sheet 1, will VLOOKUP help me sort this function to pull only the names and email addresses from sheet 2?


I have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.

Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith

So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)

For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe


I have a chart of more than one column. The first column holds names starting at A2 in aplha order. I need to scan from A2 onwards (to A200 for example) and highlight the cells that are duplicates (or triple copies). For example:

Smith, John
Smith, John
Johnson, George

In this list, Smith, John will highlight.

I have a list of names in a single cell. They are all seperated by a comma, then a space.

Example would be: John Smith, Steve Wilson, Wallace O Malley, etc.

What formula could I use to pull out the names individually, starting from the farthest right?