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

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

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 ...
3D References - Formulas that Reference Multiple Sheets at Once in Excel
Have one simple formula that will reference the same cell or range of cells on multiple worksheets at once without ...
Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...

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

Hello all.

I have a column of data (AU) that contains names of people:
John Smith
Joe Burns, Magic Johnson
Michael Jackson
Larry Bird, Magic Johnson, Frank Tank, John Smith, Marcus Walker, Joe Burns
Michael Jackson

What I want to do is to write a function that will take the above data and create a list on the same worksheet that will look like this (cut around commas and insert the values into a new row):

John Smith
Joe Burns
Magic Johnson
Michael Jackson
Larry Bird
Magic Johnson
Frank Tank
John Smith
Marcus Walker
Joe Burns
Michael Jackson

It's good that the names will repeat.

How can I do this? I am not a computer programmer so this is difficult for me! Thanks in advance. This is the logic I have so far.


With Worksheets("Sheet1").Column(1)
   'search in each cell for the commas
   Set c = .Find(",", lookin:=xlValues)

   'if there is a comma, cut and trim everything after it
   If Not c Is Nothing Then
        'Insert Row 
   End If

   'insert it into the next row
   'watch out for: if there are no commas & if there are multiple commas, to make sure to also check for commas in the newly added row
End With

Is it possible to get Excel to remember certain items typed in?

i.e. I type peoples first and last names

Richard Umney
John Smith
Dave Jones
John Smith
Jonny Jim

Then as i type the next name as i type it it looks up (in order of most used to least) the one that matches.

I type: J
Displays a list:
John Smith
Jonny Jim

I type: Jo
Displays a list:
John Smith
Jonny Jim

I type: Joh
Displays a list:
John Smith
Jonny Jim

I type: J
Displays a list:
John Smith

List a real time find

I have a simple table, two columns A and B. In B1:B40 I have a list of
names, in A1:A40 are a list of numbers sorted from highest to lowest.
Ultimately what I'm trying to do is to get a ranking of the top 5
scores but, when there is a tie (duplicates in A) to take the names in
B next to the duplicate scores and Concatenate them in C. I've tried
using Rank, Vlookup, using Excel's FILTER and none of them work for
what I'm trying to do. My thought is to try to find a function that
will look through the numbers in A and when it finds duplicates
concatenate the matching names in a cell in C but as Vlookup only
returns one result it isn't working. Anyone have any ideas?

One of the problems with these rankings is that the 'Top 5' scores could actually be down through C6 or hypothetically, even C10 or lower (see example below):

First line ("23 John Doe" is Row 1)
(example 1)

23 John Doe
22 Jane Doe
22 Bob Jones
21 John Smith
20 Lisa Johnson
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Williams
18 Luke Jenkins
17 April Jones

The results I am looking for would be:
(example 2)

23 John Doe John Doe 23
22 Jane Doe Jane Doe, Bob Jones 22
22 Bob Jones John Smith 21
21 John Smith Lisa Johnson, Sue Jones 20
20 Lisa Johnson Cathy Stanford, Jack Ford, Bill Wall 19
20 Sue Jones
19 Cathy Stanford
19 Jack Ford
19 Bill Wall
18 Luke Jenkins
17 April Jones

So in example 2, it is finding the top scores, even down through the
duplicates, and concatenating the names in C (I forgot to mention in
the first post I also need to put the number for column A next to the
result in C.

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?


I need to compare 2 lists that would consist of similar names such as:

Smith,John Smith,John E

I can compare for an exact match using
=IF(COUNTIF(B:B,A1)>0,"In List","Not In List")

but I would like it to say "In List" whether the 2nd name is
Smith,John E
Smith,John Edwards

I would appreciate any ideas.



I was wondering if it is possible to move a row of cells with (names in each cell) to a column. Basically, I want the list of names to go from top to bottom rather than left to right.

From this (each name is in separate cells)
John Jones Bob Smith Jane Morgan

to this:

John Jones
Bob Smith
Jane Morgan

Thanks to all!


I have a spreadsheet which will be used by others and I don't want to use macros.

I have a list of names, with surnames in column B and first names in column C, as follows:

Smith, John
Smith, John
Jones, Betty
Williams, Tom
Smith, John
Williams, Tom
Williams, Tom
Cooper, David

and so on.

What I want to do is, on another sheet, have each name only once, and in alphabetic order of surname. But I want to use formulae, not macros (so using the sort command is out). At the moment, I have the following formula in column A2 in front of the surname in order to get the unique occurrences of each name:

=if(countif($D$2:D2,D2)>1,"",max($A$1:A1)+1) - where column D is the surname & christian name concatenated.

Of course, when copied right down to row 50, the formula would be:


So, the result I would want on the above list would be:

Cooper, David
Jones, Betty
Smith, John
Williams, Tom

with no blank rows in between!

Any ideas?


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 a listing of names that are seperated by commas as in the following example...

Smith, Mike
Jones, Tom

I would like to know if there is a way to, in the cell the names are in, switch the data sides and erase the comma:

ex. Smith, Mike becomes Mike Smith
Jones, Tom becomes Tom Jones

Can anyone think of a way to do this?


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?



I have several 1000 names in a list. They are all in the same format:

Doe, John
Smith, Jerry

I need to quickly separate the last name and the first name into 2 different columns. Any suggestions?

Doe, John



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

What I want to do is find wherever the date (column A) and the name (column B) match then total the $ amount (Column C) into column D after the last entry so that example A (original data) turns into example B

(Example A)
04/14/10 JOHN SMITH $5.65
04/14/10 JOHN SMITH $10.35
04/14/10 JOHN SMITH $2.50
04/15/10 JOHN SMITH $3.00
04/15/10 JOHN SMITH $2.00

(Example B)
04/14/10 JOHN SMITH $5.65
04/14/10 JOHN SMITH $10.35
04/14/10 JOHN SMITH $2.50 $18.50
04/15/10 JOHN SMITH $3.00
04/15/10 JOHN SMITH $2.00 $5.00

I hope this clarifies an earlier post that may have not been too clear..

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!



I have created a member-list for a club in an excel worksheet. In another sheet, I want to create a search function to find members. How do I implement an auto-complete feature for member names?

Eg. Suppose I want to find John Smith. I have three different members named John Lim, John Sharan and John Smith. As I begin typing John, I want it to display all three names in a drop-down list automatically, so I can choose the correct "John".

Awaiting a reply soon.


i'm trying to sort through a list of names thats over 2000 entries long and i want to separate those names by surname and create an empty row of cells in between.
is this possible?

here's an example of what i have:
last name | first name | data | data
smith | john | ** | **
smith | mary | ** | **
snake | john | ** | **
sonders | mary | ** | **

here's an example of what i want the result to be:
last name | first name | data | data
smith | john | ** | **
smith | mary | ** | **

snake | john | ** | **

sonders | mary | ** | **

is this possible or do i have to separate them all out individually???