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

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

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

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

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

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

Can someone tell me how to add commas to an existing column of last names

example: Doe and I need it to read Doe,

Hi All,

I have a list of names in column A2:A30 and a copy of them names in B2:B30. i need to shuffle them names randomly but cannot assign the same name to the same person. e.g David P in A2 cannot have David P in B2 likewise Tom H in A3 cannot be assigned Tom H in B3.

Does anyone have any suggestions?


is there a way to automate =left(b1,40) and remove commas from the text?

text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.

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

Hello All - Any Help Is Appreciated

I need to copy all the rows assigned to "David" in the " CUSTOMER " column, to a template file entitled tempate.xlt . I would like to
insert them starting at A3. I also need to do the same for the remaining names (each as a separate invoice).

YET, just figuring out how to copy the rows based on the specific names would be a wonderful starting place.

Item Description Item Qty Price Total Customer Speekers 111222 1 $14.99 $14.99 David Flowers 222333 1 $177.00 $177.00 Joanne Colas 333444 3 $1.99 $5.97 David Canned Beans 444555 3 $5.99 $17.97 Joanne Tennis Shoes 555666 3 $7.99 $23.97 Teresa Green Socks 666777 1
$19.99 $19.99 Teresa

I know about AUTOFILTER, but I am hoping for a better option seeing the full spreadsheet will contain multiple names and items.

Can VBA do this?

Thank You,


David(s) - thank you for the help. Problem solved.

The Davids stick together!!!!



> I have a series of full names in single cells that are formatted the
> following way:
> <Last Name>,<Space><First Name> - thus it looks like the following:
> I would like to separate this into two columns and eliminate the comma. I
> appreciate any help. I am working in Microsoft Excel 2000 and need answers
> in Excel, not in Visual Basic.
> Thank you,

I am fairly new to using EXCEL, I have merged 3 fields of data (ie A2, B2, and C2( i.e Leigh, Damien, Barnett) into a new field and have inserted a comma and a space at the end of the 1st and second cell to separate to be grammatically correct. But Where I have no information in the second and third fields I am left with 2 extra unwanted commas ie Leigh, ,

How Can I get rid of these unwanted commas, or how can I improve my formula in the first instance to enable me to not end up with these unwanted commas?

My current formula:

=CONCATENATE(A2,", ",B2,", ",C2)



My problem is, not every cell is formatted the same. Some contain last name and first name, some contain last name and husband and wife first name with extra spaces, commas, etc....I want to keep all names and have first names in one column and last name in another. Can anyone help?

Hope that good title.

I have got table with names.(Sheet1 Column A)
Table is sorted alpabeticly.
Some names are more that ones:
Let say:

I need formula on sheet2(A1) that will put Bob in cell a A4 which is amount of times Adam names is on sheet 1.
If there was another names- let say Charlie

this formula will shift Charlie into A6 which is amonut of times Bob is on list A.

So then next name will go to A10.

I have a list of names in column B, basically assigning individuals to tasks. I want to list in column C each unique name in column B. So if the list in B is:


Then column C would produce:


What formula would accomplish this? I am thinking it is something along the lines of mod or offset?

Hi There.

I want to copy a tab and save it as a web page a variable number of times, once for each person's name on a list.

The names are in the range A3:A62
Currently there are 39 names on the list, next week it will be 41 - The names and number fluctuates by 5 - 10%

First I need to take the first name in the list and paste it as values into range BA21:BE22

Then I do some stuff which includes copying sheet to a new workbook and Copying and pasting as values - no problems here.

Finally I save as a webpage

The name in this example, Anne Leblanc is the first name on the list this week. I would like this to loop with each persons name being the name of the webpage.

With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\Users\Glen\Desktop\Anne Leblanc.htm", "Summary", "", xlHtmlStatic, _
"Book1_25114", "")
.Publish (True)
.AutoRepublish = False
End With

Can you help with the starting and ending so that this loops properly?

Any help is appreciated


Just looking for a way to force an INDIRECT formula to ignore any hyphens or brackets in the tabs names.

The formula I am using as it stands reads;


Column B holds the tab names.

However when I drag this down the list of tab names I only get figures back for the tabs which do not include brackets or hyphens. I am not able to remove these from the tab names so hopefully there is a workaround. I think I need to put single commas/quotations in somewhere but dont know where to start.

Thanks in advance.


If I have a column full of email addresses in Excel 2007 and I want to copy that column and paste it into a website that only accepts the email addresses if they are all separated by commas how do I do that without manually adding commas between each email address?



hi guys.

sorry for being a newb but would any of you
would be kind enough to show me a formula for:

what I have:

last name , first name
last name, first name
last name , first name

what i need to do:

last name, first name
last name, first name
last name, first name

as you can see, some of those last names have spaces before the commas. I need to get those spaces out.
I understand that the TRIM function only deals with the spaces before
and after and the trailing ones.

I tried to search for it, though not quite successful in my chosen keywords.

I'm just an old dog trying to learn new tricks from excel.

thanks much.

I need to create a formula (that will eventually be a macro) to alphabetize a column of names.

However, I only need to alphabetize the last names and after importing data off the web, I have both first and last names. Ex:

Joe Davis
Shawn Horne
Eric Lofgren
Steve Skrinar
Jason Lewis
David Burne

I need to write a formula that will skip or drop the first names and alphabetize like this:

these names and data imported from the web will be in a worksheet that I need to draw certain names and corresponding data into another worksheet. I am thinking of having a formula in the final worksheet that will find the appropriate name and pull the data for that person.

So in essence, I need a formula for alphabetizing and also for matching names linked to a second worksheet.


I am looking to have the Cells that I have applied a Dropdown List to update with the changes that I make to the list itself.


If I were to validate a list with a range of a1:a3 using "Bob" "Sarah" "Bill" and then apply that list to B:B and randomly select from the three names running the length of B:B, I would want that when I go back to A2 and change "Sarah" to "Linda" that every "Sarah" that I have selected using the drop down will update to say "Linda"

I hope that I am explaining myself clearly and in a simple context. This is my first Post attempt.