|
Adding Commas Between Full Names
|
|
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
Adam
Similar Excel Video Tutorials
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:
Sam
David
Kyle
Anne
and my second recordset is below:
Anne
Kyle
Sam
The second column should change to
Sam
< Empty Row >
Kyle
Anne
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
Hey all,
I have two names like this:
PRIMEAU,I & PRIMEAU,JANETTE
BAIN ED & HELEN
which are similar but different than the following:
AVERNA,ROBERT C
Corn,Marlin
Lizand,ROBERT H
CATANESE,SALVATORE J & OLGA M
JIMENEZ,ALEJANDRO & HILDA M
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
CORNWALL,A ROBERT & CORNWALL,ANNA
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
KNUD J & MARIA L HOSTRUP
RAFIK RAYES & HODA TABAR
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?
TyeJae
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.
Example:
John & Jill
James and Sue
Kerrie-Anne
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,
Chris
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?
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
1531
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.
Kai
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.
say
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=&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=&text=Sarah+Miles" target="_blank">Sarah Miles</a>, <a href="/search.php?subcat=&text=Joss+Ackland" target="_blank">Joss Ackland</a>, <a href="/search.php?subcat=&text=Greta+Scacchi" target="_blank">Greta Scacchi</a>, <a href="/search.php?subcat=&text=Charles+Dance" target="_blank">Charles Dance</a>, <a href="/search.php?subcat=&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=&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
Fred
Hi,
i have the names in column A and I need to the value next to first space that is "David" in all the cases.
A B
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.
eg:
Alan David Jones = resilt David
abcdaa 1234 = result 1234
A abcd jones sir = abcd
Thanks for the help.
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 !!
Marcos.
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)
EXAMPLE DATA:
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?
Hi,
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
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?
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
COLUMN 1
David Thomas
into
COLUMN 1 COLUMN 2
David Thomas
Any ideas?
Thanks again folks,
Darrell Leland
Hi,
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
Thanks
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):
john,mike,sarah,
sarah,mike,pete,george
george,pete,ann,clint,kevin
sarah,pete,sandy,kevin,john,george
I am interest on following persons:
john
sarah
kevin
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
row2:sarah
row3:kevin
row:john
Is this possible to build with formula?
Thanks
Hi,
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
Hi
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
I have a list of names in column C in the following format;
Abbott, Lance
Acampora, David and Janet
Adamson, Dorothy
Akkenapally, Sreetulasi
Aldo, Kristin
Alhage, Sarah
Allen, Marc J. and Susan E.
In Column A I need a formula that will return the entire first names & in column B I need a formula that will return the last name.
This formula below worked OK with returning the first name, but only when I had one first name in the field.
=IF(ISERROR(RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))),"",RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))))))
Thanks!
|
|
