Looking For A Formula To Insert Comma 


Looking For A Formula To Insert Comma  Excel 
View Answers 
Is there a formula that I can use to insert a comma? I have a formula written to combine both first name and last name using concatenate. But I need it to seperate the 2 with a comma.
Thank you so much for your help.
AuntDebbie
Thank you so much for your help.
AuntDebbie
Similar Excel Tutorials
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
How to combine a list of data into one cell while putting a delimiter between each piece of data. This tutorial us ...
How to combine a list of data into one cell while putting a delimiter between each piece of data. This tutorial us ...
Return the Min or Max Value Using a Lookup in Excel  INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Helpful Excel Macros
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
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
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
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
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Extract the Last Word from a Cell in Excel  User Defined Delimiter Text Extraction  UDF
 This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
 This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
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
 This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Similar Topics
Hi All,
I have 4 cells J  K that may or may not have a value, in Cell N2 i would like to combine the four cells J  K and insert comma separate each value only if needed. ie "Test1,Test3" With the below formula I get the following results:
If J2 is the only value: "Test1, " Comma Not needed
If J2, K2 and M2 is value: "Test,Test3Test4" No Comma separating Test3 from Test4
=CONCATENATE(IF(J2="","",J2),IF(J2="","",","),K2,IF(OR(K2="",L2=""),"",","),L2,IF(OR(L2="",M2=""),"",","),M2)
Is there an easier way to accomplish this task?
Thanks
Paul
I have 4 cells J  K that may or may not have a value, in Cell N2 i would like to combine the four cells J  K and insert comma separate each value only if needed. ie "Test1,Test3" With the below formula I get the following results:
If J2 is the only value: "Test1, " Comma Not needed
If J2, K2 and M2 is value: "Test,Test3Test4" No Comma separating Test3 from Test4
=CONCATENATE(IF(J2="","",J2),IF(J2="","",","),K2,IF(OR(K2="",L2=""),"",","),L2,IF(OR(L2="",M2=""),"",","),M2)
Is there an easier way to accomplish this task?
Thanks
Paul
Hi,
I've set up an excel spreadsheet where you insert x,y & z coordinates in columns & then I run a code which combines the values with comma in between & put it in the 4th column. But for some reason when the values get big it put in more comma's (1000 comma separated) how can I just keep the original format?
ColumnA________ColumnB_______ColumnD
134217728______268435456_____134,217,728,268,435,000
Please help...
Thanks
JC
I've set up an excel spreadsheet where you insert x,y & z coordinates in columns & then I run a code which combines the values with comma in between & put it in the 4th column. But for some reason when the values get big it put in more comma's (1000 comma separated) how can I just keep the original format?
ColumnA________ColumnB_______ColumnD
134217728______268435456_____134,217,728,268,435,000
Please help...
Thanks
JC
Hi,
I have used below function in Column D
=CONCATENATE(A2&","&B2&","&C2)
where I joined 3 columns into one separated by comma.But the problem is that when either of the row does not contain anything,it does show single comma and double comma either at the end or in between.How to get those removed those extra comma.
Pls do not use VBA.
With Best Rgds
suresh
=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))
Hi,
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
Can anyone help?
Many thanks,
Doug.
Hi,
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
Can anyone help?
Many thanks,
Doug.
I'd like to combine several text cells into one cell seperated by a comma. There are over 50 cells per row and some are null, so would be keen to generate a formula I could copy down. Would prefer not to do a large concatenate with a trim?
Thoughts?
Thoughts?
I have a list of names all in the format LASTNAME, FIRSTNAME. I would like to move the data to a new cell while removing the comma. The comma is always at the end of the last name, before the only space, and there is always only one comma. Is there an easy way to do this?
How do I create a formula that will look for the comma and then take anything after it but before the next comma into a new row? Here is a basic example
thanks,
aa,bb,cc,gg,gg, aa aa,bb,cc,gg,gg, bb aa,bb,cc,gg,gg, cc aa,bb,cc,gg,gg, gg aa,bb,cc,gg,gg, gg aa,bb,cc,gg,gg, aa,bb,cc,gg,gg, aa,bb,cc,gg,gg,
thanks,
aa,bb,cc,gg,gg, aa aa,bb,cc,gg,gg, bb aa,bb,cc,gg,gg, cc aa,bb,cc,gg,gg, gg aa,bb,cc,gg,gg, gg aa,bb,cc,gg,gg, aa,bb,cc,gg,gg, aa,bb,cc,gg,gg,
I'm trying to write a macro which searchs for a comma in every cell in column A.
If a comma is found in the cell it is suppose to duplicate the row and insert it just below.
So if the string in cell A5 is "5562288851, 5562030576", row 5 should be duplicated and inserted between row 5 and 6. And this should be checked on all rows containing data in column A.
The SEARCH/FINDfunctions does not work in VBA, how am I suppose to make this possible?
Thanks in advance, a happy hobbyist from Sweden.
If a comma is found in the cell it is suppose to duplicate the row and insert it just below.
So if the string in cell A5 is "5562288851, 5562030576", row 5 should be duplicated and inserted between row 5 and 6. And this should be checked on all rows containing data in column A.
The SEARCH/FINDfunctions does not work in VBA, how am I suppose to make this possible?
Thanks in advance, a happy hobbyist from Sweden.
I get a report several times a week consisting of several columns and some hundred rows. There must be no comma signs in column E but the reports that I get will sometimes have commas in Col E anyway.
I have a macro/vba code in another workbook that I start by a keyboard combination. This macro will adapt the look of the report, but it can't take care of the comma issue. However, I have managed to remove the comma and replace it with nothing but that is not sufficient. I want to delete the comma and all figures to the right of the comma sign. There can between 1 and 4 decimal numbers.
I need to integrate some kind of vba code that will check every cell in column E and if it finds a comma in any cell, the comma must be deleted and all the numbers to the right of the comma too.
Hope you can help me with this issue because I have been googling around for two days now... It drives me crazy.
I have a macro/vba code in another workbook that I start by a keyboard combination. This macro will adapt the look of the report, but it can't take care of the comma issue. However, I have managed to remove the comma and replace it with nothing but that is not sufficient. I want to delete the comma and all figures to the right of the comma sign. There can between 1 and 4 decimal numbers.
I need to integrate some kind of vba code that will check every cell in column E and if it finds a comma in any cell, the comma must be deleted and all the numbers to the right of the comma too.
Hope you can help me with this issue because I have been googling around for two days now... It drives me crazy.
I have in cell A1 5000
and in cell B1 50
the original figure is 5000,50
Does anyone know a formula in cell C1 that will add cell A1+cell B1 and place the comma as indicated.
I tried Concatenate but it gives 500050 and disregards the comma.
Any ideas?
and in cell B1 50
the original figure is 5000,50
Does anyone know a formula in cell C1 that will add cell A1+cell B1 and place the comma as indicated.
I tried Concatenate but it gives 500050 and disregards the comma.
Any ideas?
I have data in range A6:A60, I need to copy this data into another program which requires it on one row and obviously with there being 60 rows it only pastes the first. The data also has to be split with a comma.
What I have done in the past is :
=concatenate(A1,",",A2,",",A3... A15)
as concatenate only allows me to concatenate 15 cells, I have to perform 4 or 5 different concatenate formulas and then concatenate the results.
I want to know if there's an easier way of mashing them all up together in one cell seperated by a comma other than the way I am currently going about it?
Thanks
What I have done in the past is :
=concatenate(A1,",",A2,",",A3... A15)
as concatenate only allows me to concatenate 15 cells, I have to perform 4 or 5 different concatenate formulas and then concatenate the results.
I want to know if there's an easier way of mashing them all up together in one cell seperated by a comma other than the way I am currently going about it?
Thanks
Hello,
Please Can any body tell how to insert(add) Comma's for 1000 lines at the staring in Microsoft Excel.
For Example:
I have a list like
1000
2000
3000
..
..
..
1000 lines
My question is how can i add Comma Infront of each line(or)number..
If it's possible in Microsoft Excel then where it will be possible.
Please help me with this.
Thank you very much in Advance
swati
Please Can any body tell how to insert(add) Comma's for 1000 lines at the staring in Microsoft Excel.
For Example:
I have a list like
1000
2000
3000
..
..
..
1000 lines
My question is how can i add Comma Infront of each line(or)number..
If it's possible in Microsoft Excel then where it will be possible.
Please help me with this.
Thank you very much in Advance
swati
I am looking to take several cells,
i.e.,
1 4 5 6 7
1 3 5 2 6
1 3 4 5 6
2 4 5 6 7
and combine into one cell with comma after each number.
1,4,5,6,7,1,3,5,........7,
I have been using the =concatenate() but it gets very tedious for many cells
Any shortcuts?
i.e.,
1 4 5 6 7
1 3 5 2 6
1 3 4 5 6
2 4 5 6 7
and combine into one cell with comma after each number.
1,4,5,6,7,1,3,5,........7,
I have been using the =concatenate() but it gets very tedious for many cells
Any shortcuts?
Hi I just joined MREXCEL club after I found most of my excel answers from here when googling, and found folks here are really helpful.
As my first thread and question, I would like to learn how to add a comma after the first name in the cell. I have a list of names in one column as LASTNAME FIRSTNAME. I want to add a comma after the last name, without doing it in a new column. I'm assuming this has to be done thru VB? but I dont know anything about the codes yet. If anyone could help me out I would truly appreciate it!! Also, I want to add a comma after the last name only; so if I have a name that has three parts (e.g Jane Doe Jr.), only one comma is added after "Jane".
Thanks in advance for any help!
As my first thread and question, I would like to learn how to add a comma after the first name in the cell. I have a list of names in one column as LASTNAME FIRSTNAME. I want to add a comma after the last name, without doing it in a new column. I'm assuming this has to be done thru VB? but I dont know anything about the codes yet. If anyone could help me out I would truly appreciate it!! Also, I want to add a comma after the last name only; so if I have a name that has three parts (e.g Jane Doe Jr.), only one comma is added after "Jane".
Thanks in advance for any help!
Hello,
The following function works fine:
=DSUM(DataTable,"FTE", A30:A31)
However, for the next row I would like to evaluate the following, because I want to skip row 31 (A30,A32 being a range):
=DSUM(DataTable,"FTE", A30,A32)
However, because the formula requires a comma to delimit the formula, a range containing a comma gives an error.
How can I define the range cell A30 and cell A32 (excluding 31) without using a comma, or within a formula?
Any help would be greatly appreciated!
The following function works fine:
=DSUM(DataTable,"FTE", A30:A31)
However, for the next row I would like to evaluate the following, because I want to skip row 31 (A30,A32 being a range):
=DSUM(DataTable,"FTE", A30,A32)
However, because the formula requires a comma to delimit the formula, a range containing a comma gives an error.
How can I define the range cell A30 and cell A32 (excluding 31) without using a comma, or within a formula?
Any help would be greatly appreciated!
Greetings,
I am using a formula in some cells which combines numbers and text, e.g.,
=F2285&", "
This creates a numerical value with a comma and a space after the number.
The resulting value would be something like this ( a number followed by a comma and a space)
1245,
I want to format the number part of the value, so that it includes a comma to denote thousands, i.e. the value should look like this
1,245,
(i.e. the number using a comma to denote thousands, followed by a comma and a space.)
I thought some kind of formatting switch included in the formula might work, but can't figure out how to do that. Any ideas?
Many thanks,
jeannie
I am using a formula in some cells which combines numbers and text, e.g.,
=F2285&", "
This creates a numerical value with a comma and a space after the number.
The resulting value would be something like this ( a number followed by a comma and a space)
1245,
I want to format the number part of the value, so that it includes a comma to denote thousands, i.e. the value should look like this
1,245,
(i.e. the number using a comma to denote thousands, followed by a comma and a space.)
I thought some kind of formatting switch included in the formula might work, but can't figure out how to do that. Any ideas?
Many thanks,
jeannie
I have an excel list of about 300+ names. Each name is in the same cell written as "LAST NAME FIRST NAME MIDDLE INITIAL" with no commas. (Ex. Doe Jane A). I need a way to insert a comma ONLY after the first word in the cell so it will read "Doe, Jane A". Is there any way to do this without having to do this all manually?
Thanks!
Thanks!
I have a column with over 6000 entries and I want to =concatenate groups of 16 cells of the column into individual rows with the data field separated by a comma. i.e. a row of A1,A2,A3.....
Is there a way to =conatenate(text) a formula were it will automatically include the following 15 cells in a column after pasteing a formula in a cell, without having to assign the cells such as (A1:A16) along with the comma seperated field function
Is there a way to =conatenate(text) a formula were it will automatically include the following 15 cells in a column after pasteing a formula in a cell, without having to assign the cells such as (A1:A16) along with the comma seperated field function
Hi  I need a formula that will insert single quotes ' before and after a comma,
and that also insters quotes at the start and end of the whole string.
So for example the string looks like this:
DDD,SSS,AAA,FFF
the formula will make it look like this:
'DDD','SSS','AAA',FFF'
Thanks for your help!
I have a column which has some addresses in each cell, with each part of the address seperated by a comma and then a space.
e.g.
10 london street, The place, London, UK
10 canada street, canadaville, zip code, Canada
I want to create a new column where it only shows what appears after the last comma, i.e. the country, so a formula to get rid of everything bar what appears after the last comma.
So the above two cells would be:
UK
Canada
Thanks!!
e.g.
10 london street, The place, London, UK
10 canada street, canadaville, zip code, Canada
I want to create a new column where it only shows what appears after the last comma, i.e. the country, so a formula to get rid of everything bar what appears after the last comma.
So the above two cells would be:
UK
Canada
Thanks!!
Not sure what is the best formula to use for the following scenario:
Sample: Cell A1 contains 1000 words and some are followed by an comma (red apples, oranges, yellow spotted bananas, etc).
I need a formula in cell B1 that will copy the first 1024 characters rounding down to the last full word left of the comma. Another formula in cell B2 that will continue from the next word right of the comma where cell B1 left off and copy the next 1024 characters rounding down to the last full word left of the comma. Repeat in cell B3 except it will continue from the next word right of the comma where cell B2 left off and copy the next 1024 characters rounding down to the last full word left of the comma. Etc., etc., etc.
Any help would be appreciated sorry if I didn't express my dilemma properly, I can provide more clarification if needed.
Sample: Cell A1 contains 1000 words and some are followed by an comma (red apples, oranges, yellow spotted bananas, etc).
I need a formula in cell B1 that will copy the first 1024 characters rounding down to the last full word left of the comma. Another formula in cell B2 that will continue from the next word right of the comma where cell B1 left off and copy the next 1024 characters rounding down to the last full word left of the comma. Repeat in cell B3 except it will continue from the next word right of the comma where cell B2 left off and copy the next 1024 characters rounding down to the last full word left of the comma. Etc., etc., etc.
Any help would be appreciated sorry if I didn't express my dilemma properly, I can provide more clarification if needed.
I have a date in A1, listed as May 09 2009. Because there is no comma in the middle, it is not recognized as an actual Date.
In Cell B1, I'm trying to bring over A1 value and add a comma to it so the end result is May 09, 2009
Any thoughts?
thanks!
In Cell B1, I'm trying to bring over A1 value and add a comma to it so the end result is May 09, 2009
Any thoughts?
thanks!
Hi There,
I'm doing data clean up for a client & have run across data that contains a comma at the end. They've supplied it to me this way & from what I can see it just ends (there is no space after the final comma) IE:
Cheerful, Chipper, Convivial, Delighted, Ecstatic, Elated, Exultant, Pleasant, Pleased, Sparkling, Sunny, Tranquil, Unadorned, Symphony,
The next row may be similar in descriptors but not contain the extraneous comma at the end. IE:
Compassionate, Tender, Calm, Countrified, Priestly, Tranquil, Unadorned, Symphony, Pastoral
Is there a way to remove the final comma in the fields where it's just "dangling" at the end but not remove it from the fields that don't contain the extraneous comma?
Obviously Find & Replace doesn't work because I need the commas to remain throughout the rest of the data. I've searched the boards & have not found a formula that works...if I've missed something that's already posted I apologize. I'm going a bit cross eyed at the moment & with thousands of rows to go through, I'd hate to have to remove the ending comma's one at a time!
Many thanks,
pinkgemini
I'm doing data clean up for a client & have run across data that contains a comma at the end. They've supplied it to me this way & from what I can see it just ends (there is no space after the final comma) IE:
Cheerful, Chipper, Convivial, Delighted, Ecstatic, Elated, Exultant, Pleasant, Pleased, Sparkling, Sunny, Tranquil, Unadorned, Symphony,
The next row may be similar in descriptors but not contain the extraneous comma at the end. IE:
Compassionate, Tender, Calm, Countrified, Priestly, Tranquil, Unadorned, Symphony, Pastoral
Is there a way to remove the final comma in the fields where it's just "dangling" at the end but not remove it from the fields that don't contain the extraneous comma?
Obviously Find & Replace doesn't work because I need the commas to remain throughout the rest of the data. I've searched the boards & have not found a formula that works...if I've missed something that's already posted I apologize. I'm going a bit cross eyed at the moment & with thousands of rows to go through, I'd hate to have to remove the ending comma's one at a time!
Many thanks,
pinkgemini
Is there a way to get Excel to insert the comma every third digit, in a number? For example, I have this number:
6903434000.00
Now I don't need the two digits to the right of the decimal, but is there a way to format the cells so that it automatically becomes 6,903,434,000 (with or without the decimals)?
6903434000.00
Now I don't need the two digits to the right of the decimal, but is there a way to format the cells so that it automatically becomes 6,903,434,000 (with or without the decimals)?
I have a spreadsheet in which I use a match function to find the row a name is on. The name may be listed several times and the name has a comma in it, for example, "Tom, George". I got it to work once, but it doesn't always work. Is it because of the comma or because it is in the lookup several times? It's my understanding the Excel will return the row number for the first time it sees the name in the list, which is what I want.
Any ideas on why I get NA# with the name with the comma?
Many thanks for some help, especially since I got it to work once with the comma.
Any ideas on why I get NA# with the name with the comma?
Many thanks for some help, especially since I got it to work once with the comma.