Email:      Pass:    Pass?

Free Excel Forum

Adding Commas Between Full Names

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

I'm sure this is an easy formula but I need some help.

I have a line with names and want to add commas after only the complete names not in every space.

Example: Shia LaBeouf David Morse Sarah Roemer Carrie-Anne Moss

What formula would get

Shia LaBeouf, David Morse, Sarah Roemer, Carrie-Anne Moss

Thanks for any help


Similar Excel Video Tutorials

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
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics

Hello Excel Champs ,

I have a list of names in one sheet and another set of names (where some of the names in the first list may be missing). Now, I would like to Sort this second data set as per the first & complete set of names.

If a particular name is not appearing in the second record set, a blank row should be created on its behalf.

For example,

If my first recordset is below:


and my second recordset is below:


The second column should change to

< Empty Row >

Is it possible to do so (either by using Macros or not)

Thanks in advance for your helps....!!!

=SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon, Carrie'!C4:C35)+SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon, Carrie'!D4:D35)+SUMPRODUCT('Deacon, Carrie'!E4:E35,'Deacon, Carrie'!F4:F35)

I'm using the formula above to calculate some stats for a call center. On a master "group summary page", I need to get the sum of the results of this formula from 20 worksheets. Other than duplicating the formula and changing the names, is there a faster way to do this? The cell references are identical for all, worksheets just the names change.

The only thing I can think of is "(formula using name 1) + (formula using name 2)..." and so on for every sheet in the book. This will be incredibly long and it seems to me there has to be a better way. Any ideas?

Can you have a vlookup with a SUM function, i.e. enter the name 'Adam' and it finds all enteries of 'Adam' and Sums the values aginast him? example below?

A B C D E 1 Day1 Sum Total 2 Adam 1 Adam 9 3 Jon 4 4 Paul 8 5 David 2 6 Sarah 6 7 Julie 5 8 Bryan 10 9 Jenna 2 10 11 Day2 12 Adam 7 13 Bryan 8 14 David 1 15 Jenna 0 16 Jon 0 17 Julie 9 18 Paul 6 19 Sarah 12 20 21 Day3 22 Sarah 1 23 Paul 1 24 Julie 11 25 Jon 4 26 Jenna 3 27 David 7 28 Bryan 8 29 Adam 1

Hey all, this formula almost does the job:

=IF(FIND(" ",A1&" ")-LEN(A1)-1,LEFT(A1,FIND(" ",A1)+FIND(" ",TRIM(
MID(A1,FIND(" ",A1)+1,255))&" ")),LEFT(A1,FIND(" ",A1&" ",FIND(" ",A1&" ")+2)))

But as you can see in below example, when the last name contains a space (e.g. Martin Marie Anne Hernara), it clips away Anne Hernara, just leaving Martin Marie. I want it to return Martin Marie Anna and just clip out the Hernara at end.

AVERNA ROBERT C AVERNA ROBERT Corn Marlin Corn Marlin Lizand ROBERT H Lizand ROBERT CATANESE OLGA M CATANESE OLGA JIMENEZ HILDA M JIMENEZ HILDA Corn Marlin Corn Marlin Hernara Marie Anne Martin Hernara Marie White John M White John Hernara Marie Anne Martin Hernara Marie Hernara Martin Marie Anne Hernara Martin Hernara -Martin Marie Anne Hernara -Martin HernaraMartin Marie Anne HernaraMartin Marie MartinHernara Marie Anne MartinHernara Marie Martin Marie Anne Hernara Martin Marie CORNWALL ANNA CORNWALL ANNA HernaraMartin Marie Anne HernaraMartin Marie HernaraMartin Marie HernaraMartin Marie Cortes Ann Cortes Ann Smith Marie Anne Smith Marie Sarah Ann Little Junior Sarah Ann Marie Anne Marie Anne HOSTRUP KNUD J HOSTRUP KNUD TABAR RAFIK RAYES TABAR RAFIK PRIMEAU PRIMEAU HELEN BAIN ED HELEN BAIN HELEN BAIN ED HELEN BAIN PRIMEAU PRIMEAU TABAR RAFIK RAYES TABAR RAFIK Hernara Martin Marie Anne Hernara Martin Thanks for response.

i am after a way for finding out if a person is a i certian group
Danni O'Hara Sarah Reed Sarah Reed t2
Edward Moss Sarah Reed Steve Kenny t1
Gemma Canwell Sarah Reed Dave Cross col
Helen Jefferson Sarah Reed Steve Carroll col
Wayne Glasgow Sarah Reed Bev Poad t2
wat i have is the first colum is the agents name the second colum is team leaders name the next 2 colums is the team leaders name and group assigned to
so basically i need a formula that will compare agent against tm name and give group assigned


I have a problem that I am not sure whether Excel (2010) can solve or not - my searches online so far today (and my own attempts) have not been successful...

I have 2 columns (on 2 different sheets as they are from different data connections):
Column A

Column B

Is there a way to display the names that DO NOT appear in Column B? i.e., in the above example, I would like to display the names John, Sarah and Rachel.

Many thanks

Hey all,

I have two names like this:


which are similar but different than the following:

Hernara,Harry & Marie Anne Martin
White,Alex & John M
Hernara,Marie Anne Martin
Hernara Martin,Harry & Marie Anne
Hernara -Martin,Harry & Marie Anne
Martin,Harry & Marie Anne Hernara
HernaraMartin,Harry & Marie Anne & Ann Sally
HernaraMartin,Harry & Marie & Ann Sally
HernaraMartin,Harry & Cortes,Ann
HernaraMartin,Harry & Smith,Marie Anne & Jenners,Ann Sally
Sarah Ann Little Junior
HernaraMartin,Harry & Marie,Anne & Ann Sally

Basically, what makes the 2 instances different from the above list is the location of word(s),space, and how many word(s) before/after ampersand.

In essence, I am looking to create an if condition like below, which will only meet the criteria of first two instances:

If(character(s) then comma then character(s) then space then ampersand then space then character(s) then comma then character(s),IF(ISNUMBER(A1,FIND(","))),"a","b","")

IF(character(s) then space then character(s) then space then ampersand then space then character(s),IF(ISNUMBER(A1,FIND(","))),"a","b","")

Is it possible to create and if statement like the two above in Excel? Note I have very little VBA knowledge.

Thanks for response

Ok here is my dilema. I have a list of names. At the press of a button I want to have the top person on the list of names to go to the bottom and the full list of names shift upwards so there is no empty space. For example...

a1= Susie
a2= John
a3= Bob
a4= Sarah

Now when I press the button I want it to read...

a1= John
a2= Bob
a3= Sarah
a4= Susie

Then of course if I pressed it again...

a1= Bob
a2= Sarah
a3= Susie
a4= John

And so on...

Could anyone help me out with this?


Hello, could someone please help me with the following?

In column G of my excel 2003 worksheet named: Data, I have a list of people's first names. These names were extracted from an external database.

Some of the first names actually contain peoples second names or partner's names.


John & Jill
James and Sue

Joanna Lyn

I need a macro to please delete people's second name.

So in the example above I need to keep all names that are joined by: & or and or - (such as John & Jill, James and Sue, Kerrie-Anne).

Also, in the example above I need to delete all names that are joined by a <space> (such as Joanna Lyn).

Any help would be greatly appreciated.

Kind regards,


I have a large table in which I have information on a number of employees.

I want to create a smaller table on another spreadsheet that will summarise this info. By typing in an age, for example, I want my table to VLOOKUP employees of that age and then in the new table give names and other info for all employees of that age.

However, when I try to do this, VLOOKUP always gives me info for the first employee it finds in the table, so instead of getting the following for example:

Age Name

25 Anne
25 James
25 John
25 Tom
25 Victor

I get the following:

Age Name

25 Anne
25 Anne
25 Anne
25 Anne
25 Anne

Does anyone know what I could do about this?

I have a column of 5 rows, each cell has a name in it, and i want to count down the column and return the text if it occurs 3 or more times.
For example in column A,

A1 = David
A2 = David
A3 = David
A4 = Sarah
A5 = Sarah

So i want to return David for this column.

and if in column B,

B1 = David
B2 = Blank
B3 = Blank
B4 = Sarah
B5 = Sarah

Then i want to return the word "various"
If 3 or more cells are blank then i want to return blank.
Thanks any help appreciated.


Hi All,

On a weekly basis I receive sales data from our various sites and field opt people, however as we have staff turnover the names are changing all the time and it's difficult to have a static list to create a sales summary of individual performance by week /month etc.

I want to create a list from a column, but not by using the standard pull-down filter technique. I want to use a formula to query column A, to identify "name" and then I will just create a SUMIF function against the names. But how do I create the formula to create the list?

So for example;

Name Sale
Ava 70
Ava 63
David 89
Jo 84
Jo 59
Jo 66
Paul 98
Paul 100
Paul 93
Philip 96
Richard 90
Richard 56
Rod 80
Rod 94
Rod 52
Sarah 94
Stewart 63
Stewart 88
Stewart 96
Total 1531

Turns into;

Summary Total
Ava 133
David 89
Jo 209
Paul 291
Philip 96
Richard 146
Rod 226
Sarah 94
Stewart 247


Ladies, Gentlemen
I have following task:
I have description of thousands of movies in excel table format.
There is one column for directors names and one for actors names.
Each cell in these columns may contain none, one or more names, if more than one they are separated by commas.
A2=Sarah Miles, Joss Ackland, Greta Scacchi, Charles Dance, Geraldine Chaplin

I need to "linkyfy" them - convert each of these names into fargments of html so when they are published in a online shop they become links to search results containing themselves, ie
Sarah Miles becomes
HTML Code:

<a href="/search.php?subcat=&amp;text=Sarah+Miles" target="_blank">Sarah Miles</a>

so it will look like
Sarah Miles

But all names in a result must be still in one cell, separated by commas as in the source so A2 becomes:
HTML Code:

<a href="/search.php?subcat=&amp;text=Sarah+Miles" target="_blank">Sarah Miles</a>, <a href="/search.php?subcat=&amp;text=Joss+Ackland" target="_blank">Joss Ackland</a>, <a href="/search.php?subcat=&amp;text=Greta+Scacchi" target="_blank">Greta Scacchi</a>, <a href="/search.php?subcat=&amp;text=Charles+Dance" target="_blank">Charles Dance</a>, <a href="/search.php?subcat=&amp;text=Geraldine+Chaplin" target="_blank">Geraldine Chaplin</a>

so it looks like this:
Sarah Miles, Joss Ackland, Greta Scacchi, Charles Dance, Geraldine Chaplin

So far I have managed to do it by splitting these cells to columns by commas, applying concatenate with parts of the "link" to each one separately then merging them together.
Very messy and headache inducing procedure (and error prone too).

What I need is a formula that for given structure of the "link" say:

HTML Code:

<a href="/search.php?subcat=&amp;text=FIRSTNAME+MIDDLENAME+SECONDNAME" target="_blank">FIRSTNAME MIDDLENAME SECONDNAME</a>

could be applied in one go to a range of cells containing these names.
Please note in firts part of the link the spaces have to be replaced by "+"
Also, if the cell is empty we skip it.

As you know by now I am not an Excel expert by no means so every bit of help will be appreciated

Thank you

Hi there... Hope you are fine !!!

Is there any way to classify data following this: (???)

I type...
n C5: Mary
result: C5: Mary
in C9: John
result: C5: John..... C9: Mary
in C13: Anne
result: C5: Anne.... C9: John.... C13: Mary

and so on til line 41... Ten names automatically classified while I type. I need three blank lines between the typed names, and when I'm in the last one (line 41), I'll put other ten names, this time in column E (I can not use the column D), but I need the classification still be active... So, if in E5 I type "Alan", the result now must be:

C5: Alan.... C9: Anne.... C13: John............................E5: Mary

Is it possible ??? How ????

In advance, tanks for help !!



i have the names in column A and I need to the value next to first space that is "David" in all the cases.
Alan David =RIGHT(A2,LEN(A2)-FIND(" ",A2))

If I use the above formula in I get David as answer which is fine.
but if the values in Col A1 is "Alan David Jones" and if I use the formula I get David Jones, but i need only David as result.

In short what the ever the values exists in Column A, I need the WORD just after the first space.
Alan David Jones = resilt David
abcdaa 1234 = result 1234
A abcd jones sir = abcd

Thanks for the help.

Hi all,

I have an ever-changing list of data with names and scores. I want to match a name to one in the list and find the average of all scores for that particular name.

For example:

Name Y/N Score
Bill Y 90
Tom N 95
Sarah Y 72
Tom Y 89
David Y 90
Bill N 87
Sarah Y 87
Bill Y 93
David Y 88
Steph Y 97
Greg N 91

I want a function that will search all of Bill's scores and get the average for them. I then want the cell below that one to search all of Tom's scores and give me the average...and so on...

I've included an example spreadsheet as well.

Any help would be greatly appreciated.

Thank you in advance,



I have a list of names and I want to count how many times they appear and display a summary at the bottom of the sheet. The names and number of times they appear will change everyday)


jacob wrote 3 letters
sarah wrote 1 letter
sarah wrote 2 letters
sarah wrote 4 letters
sarah wrote 2 letters
sarah wrote 1 letter
randy wrote 9 letters
randy wrote 3 letters
xavier wrote 7 letters
xavier wrote 4 letters

Summary will look something like this:

jacob wrote 3 letters
sarah wrote 10 letters
randy wrote 12 letters
xavier wrote 11 letters

It seems like a simple answer to me, but I am missing something.

I appreciate any and all help I receive - Jimmy in Texas

I combined the names like this: LastnameFirstname. However, some have commas after the last name. Is there an easy way to parse through the list and remove the commas?

So here's my dilema. On on sheet have a list of names:

Name Tom Smith Jenny Williams Joe Brown Matt Baker Tiffany Anne

and on another sheet I have somewhat similar (sometimes exact) names:

Name Tom K. Smith Michael Stevens Tiffany Amber Anne Reggie James Matt T. Baker

The one's I underlined are ones where that particular person shares the same first and last name with someone on the previous list. What I'm looking for is a (via a formula - i'd like to not do it in VBA if possible) to compare the first list with the 2nd list and see which names share the same first/last name. Anything in the middle I don't care about.

Any suggestions?


In the range U5:U22, I have either cells that are empty or cells that contain names. In cell U25, I would like to write a formula that lists all the names, separated by commas.

Any suggestions?

Thanks for your time!

Hi guys

a pretty simple one here.

I have a list of staff names in alphabetical order which I use in a drop down list to enter into a roster. I need the (probably simple) formula to highlight certain names if selcted (i.e. names of staff not trained for a certain role) there may be 7 or 8 names which need to be highlighted.

adam brown
bill gates
charles wrat
david peters
edward fox etc etc

if I choose bill gates and david peters from the list for the cell im inputting to how can I highlight this.

thanks in advance

I am having a problem with writing the ifs and ors.
My spreadsheet is basically the first sheet with different positions within a company and beside their names are three columns of rates.

A B C D 1 Anne 2 4 6 2 Joe 1 2 3 3 Jon 7 8 9 4 Mike 5 4 6 Position Normal Time + 1/2 Double

So What i want to be able to do is type in anne and bingo her name pops up with all the rates beside her name
or joe or jon or mike
I want to be able to do this in any cell in column A as i have about 200 names.

If any one can help i would appreciate it so so much


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 would appreciate if one you guys can help me out.

Let say that I have 4 cells of data (each cell contains names separated by comma):


I am interest on following persons:

I need to figure out how I can build formula where I can find which one of these 3 persons was the last value on the 4 cells above. e.g

I would need the formula to return for row1: sarah

Is this possible to build with formula?



I'm sure this has been addressed previously but here goes.

I'm looking for a formula solution to return the result of names even through the names may appear more than once in a list. Below is some sample data, example I would want to look up say Noah and return 2 results as Noah appears twice in the list, result 1 would be 333 and result 2 would be 200. VLOOKUP will only return the 1st instance... Any help would be great.

A B 1 NAME SUM 2 Noah 333 3 Adam 555 4 Sarah 777 5 Isaac 100 6 Noah 200 7 Sarah 400 8 Rebecca 700 9 Rebecca 388 10 Jacoob 795