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


View Answers     

Similar Excel Tutorials

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 ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
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

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


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

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


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


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


The report I need to use brings back certain information like this:

Pieter Pieter Stander Abigail Abigail Birch Liam Kevin Morgan Adam Adam Beer Sarah Sarah Robinson

I need to bring back just the first name. Obviously these will not always be the same length so i cant use the LEFT formula but is there any way of getting a formula to always bring back a whole word that is before a space?

I cant think of any other quick way of doing it

Hey All,

I've been googling and visiting diferent sites for the last day or so, looking for a way to change name listings. I wish to convert the listed names in the client files, but I can't seem to get anywhere. What I want is:


Smith, John A.-----------John A. Smith
Jones, A. Larry-----------A. Larry Jones
Ching, Jon L. T.----------Jon L. T. Ching
Morse, Mary A.-----------Mary A. Morse

I've come close a few times without the Middle Initial, but I can't seem to get rid of the coma. If anyone can give me a hand, Id really appreciate it. I've got LEFTS, RIGHTS, MIDS and LENS falling out of my head into a little pile on the keyboard.

Thanks for looking,



I'm looking to count in column E how many times several criteria arise. In this case, it is names. For instance, I'd like to count how many times the names Joe, John, and Sarah appear separately. I tried the below formula, but it is returning 0.


Thanks for any help offered!

I receive an excel spreadsheet with single column of names: Up to 5000 rows with 1 - 30 names per cell (names are delineated by commas):

betty johnson,bart wong,alissa villa,marlee rodriguez,amy luce,isaac pierce,janet gales,david green,pete davidson
david green,demetrius washington,alissa villa,mike harris,tom serret
amy luce,angel cosh,david green,alissa villa
amy luce,

Would like to consolidate into one column, multiple rows, one name per cell, with duplicates deleted:

alissa villa
amy luce
annette sanborn
barthomele kramer
betty johnson

Hi guys, an easy question but I'm stuck!

I have a spreadsheet with a list of names on column A. Each person's two names (first name and surname) are separated by a space. I need a formula that will return the first name only on Column B and the surname only on Column C. Which formula should I use?
Of course, each person's name has a different number of characters, so I'm lost on how to pick the text to the left of the space separator. Here is a list of the names for an example:


Any ideas?


Hello All,

I am not too sure how to explain it so I will provide an example.

Column C represents how many times that name was shown. Is there an easy way to do this or a simple formula? I appreciate the help

Phil Phil 3
Phil Bob 2
Phil Anne 4
Bob John 2


Phil C.

I have two columns, A and B. Column A contains a list of names and B contains a ID number. The ID number represents which people are associated with a particular group.

For example,


I want to search the two columns for all the names that match "1" in Column B, and then populate a row on a different worksheet (inside the same file) with the names.

End Result should look like the following:

Random Row:// John-----Sarah-----Josh

I am using MacOffice 08 so this has to be formula driven or based on conditional formatting. No VBA.

I have banged my head on this for some time but I can't seem to figure out how to do this? I have roughly 60 names in a list and 10 different groups. It would be a huge IF statement to make it happen the only way I can think of.

Any advice on solving this problem?

Thank you in advance for any advice or help

Everyone, I need help in splitting the names in a file without using an actual formula instead of the text to columns function.
I have a cell that has first, middle and last names and need to create a cell that has only the first 5 characters of the last name.

For example: David Alan Washington

Need a cell with: Washi

Is there a formula that says to find the last space and then the first 5 characters to the right of the last space.