Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Looking For A Formula To Insert Comma

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

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

View Answers     

Similar Excel Video Tutorials

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

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


Hi,

I've set up an excel spreadsheet where you insert x,y & z co-ordinates 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

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


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?

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,


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?


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/FIND-functions 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.


Help

I have a spreadsheet that I have not used in quite a while, infact I now have a different PC. Within the spreadsheet I have formulas with "IF" function formulas. However the comma's which seperate the arguments have changed into small square boxes, and when I try to change the formulas in any way I get message saying I have missing parenthesis. This also happens if I type a new formula with comma's seperating the arguments. Basically the spreadsheet won't recognise comma's in new formulas, and has changed comma's in existing formulae to square boxes !!

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


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


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!

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?


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!


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


have to write correct formula for this scenario:

cell a1 has a name in the formal of last name, first name. create a formula that finds the comma and then returns only the last name without the comma, space following the comma, or first name. for example, "smith, adam" in cell a1 would return a value of "smith" in this cell.


Please let me know if you can help thanks a lot.


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!


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


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


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.

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!


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


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


I am using tera data.In this db it will accept comma as null value.
for eg i am inserting values on emp table .
n1,n2,n3 field will consider as null value..so i gave only comma..
so it will consider as an empty .
I am going to insert lacs of record in my table.so the date may contain
values or maynot contain values.so how should i replace the single quotes
before the date field cell
insert into emp (name,no,n1,n2,n3,date) values( ' xxx', 50 , , ,' 5/5/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' rtt', 50 , , ,' 5/6/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' mtt', 50 , , ,' ')
insert into emp (name,no,n1,n2,n3,date) values( ' dd', 50 , , ,' ')
insert into emp (name,no,n1,n2,n3,date) values( ' oo', 50 , , ,' 5/9/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' vv', 50 , , ,' 5/10/2010 ')
I want the o/p like this format.i gave only sample record.

it has to come around 10 million record
insert into emp (name,no,n1,n2,n3,date) values( ' xxx', 50 , , ,' 5/5/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' rtt', 50 , , ,' 5/6/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' mtt', 50 , , , )
insert into emp (name,no,n1,n2,n3,date) values( ' dd', 50 , , , )
insert into emp (name,no,n1,n2,n3,date) values( ' oo', 50 , , ,' 5/9/2010 ')
insert into emp (name,no,n1,n2,n3,date) values( ' vv', 50 , , ,' 5/10/2010 ')

Plz help me ..how to write formula to replace sinlge quotes ?

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.