Subscribe for Free Excel tips & more!
E-mail:

# Looking For A Formula To Insert Comma

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

## 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 ...
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 ...
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 ...
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 ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...

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

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

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

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!

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

I have some cells with a leading comma as the first character in a column.

Is there a formula to detect if there is a comma as the first character in the cell, and if found remove it?

Hi all,

I have one column with text/numbers in each cell. I would like to merge the entire column into one cell with a comma separating the entries.

Right now I'm using

=B2&","&B3&","&B4&","&B5&","&B6 etc.. etc... for the entire column.

Is there a faster formula that would do this?

Thanks for your help!

I have a set of data in Column A1 to A100

i need a macro to replace the comma separator to a dot but at the same time, dot separator to a comma separator ?
but if the number has only comma separator or only dot separator no action should be taken

for example
1,765.12 should be like 1.765,12

but
if the number contains only dot or only comma no action should be taken
for example

1765,12 number should not be changed

I need to import .csv files into excel. Problem is that the program
that exports the .csv files comma sepatates the values and the values
themselves contain commas. For example:

3,14,-1,28,0,74

Should be interpreted as:

3,14 -1,28 0,74

The only thing I have going for me is that all the values contain
commas. This means I should be able to replace every second comma with
another delimiter and thus be able to read the file correctly.
Something like this:

3,14;-1,28;0,74

IĀ“ve been thinking about how to go about this but havenĀ“t been able to
come up with anything that works. Does anyone have any suggestions on
how to replace every second comma in a string?

Regards / Thomas L

Hi, I've tried using the search function to find the answer on my own, but I wasn't able to solve my problem.

If someone is able to help me out I'd really appreciate it. I don't know VB so an excel formula is what I really need.

Here is what I want to do. I need to extract

Here is an example:
3M EMI Copper Foil Shielding Tape 1181, 2 in x 18 yd, (50,80 mm x 1,5 m), 5 per case

I need to get the string before the first comma "3M(TM) EMI Copper Foil Shielding Tape 1181".

And also the data after the first comma and before the last comma. Often I can have 4-5 (or more) commas. So I'd need to get " 2 in x 18 yd, (50,80 mm x 1,5 m)" out of that.

Thanks so much!

Hi,

I have a column of cells with text like this:
Pour-Over Coffee Urn, electric, single 3 gallon cap., brews in 1 lb. batches, side mounted control panel, s/s construction

I am trying to copy all text before the second or third instance of the comma into an adjacent cell.

I've been using: =LEFT(C548,(FIND(",",C548,1)-1))
But that gives me only the text before the first comma. How do I change which instance of the comma in my formula?

Thank you!!!!

Hi Everyone! I have been having an issue when entering formulas for quite some time and cannot figure out how to fix this so thought I would post here and see if anyone else has had this problem and knows the resolution.

Prior to upgrading to Excel 2010 I was using Excel 2007. I received a file that had been modified in Excel 2010 via email...I opened this file and made some modifications to the formulas and noticed that all of the formulas in that file used a pipe instead of a comma. Now all files I have created, modified, saved, etc...ever since in Excel 2007 (and now Excel 2010 since I upgraded) require a pipe. If I try to use a comma I get the message saying the formula I typed contains an error. The formula hints even appear with a pipe instead of a comma. I also recently noticed that when I enter a formula in Access it behaves the same way (requires a pipe), which in turn causes problems in Access.

I would like to change this back so I can once again use commas in my formulas instead of the pipe character.

Example:

normal formula =IF(A1>A2,"Yes","No")
my Excel formula =IF(A1>A2|"Yes"|"No")

I hope someone can help....this is really very annoying since I have used the comma for many many many years, and it is MUCH easier to type than the | character (since it requires a SHIFT and a pinky stretch ). Thank you in advance for any advice!!

Hi,

I'm working in an application that generates an excel file with a data
validation list. The problem its because my values im putting in that list
also contains comma characters, but i dont want to split up this value.
Someone knows if there is a special character i can put at the string to
appears as a comma when using the dropdown?

Thanks
Christiano

I have a column that looks like the example below. I'd like to find a formula that I can enter into the adjacent column that will extract everything before the last comma starting from the left.

The example below starts in A1. The result that I would like is in column B.

Can someone help me with this?

Thanks!

\$203.59, \$203.59 \$4,899.33, \$4,899.33 \$119.58, \$119.58 \$22.33, \$22.33

Hi, I have a problem in data I got which given in comma(,) as separation for decimal places. How can I convert automatically all of those comma sign to point(.) in excel other than change them in traditional way which deleting them one by one?

eg.
294,78 3,29 0,89 27,05
296,49 18,36 5,21 28,38
298,20 20,96 4,94 23,57
299,90 22,21 5,79 26,07
301,61 34,95 10,33 29,56
303,31 51,81 13,48 26,02
305,01 66,89 15,45 23,10
306,71 79,63 23,74 29,81

I have two cells containing comma seperate strings of text. I would like to compare them and highlight any matches in the string.

Eg
A1= a, b, c, d, e
A2= a , f, e , g, h.

The list are in no particular order or length and can contain any number of values. all seperated by comma space.

i'm a complete excel novice, but i think this should be pretty easy for you guys...

i just bought a new satnav system after 4 years with tomtom...

anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:

-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,

thing is, most of them don't have the 2 commas at the end and just end with the number, like this:

-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247

therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...

i'm guessing for anyone with excel experience, this should be pretty easy to solve...

Hello everyone,

Is there anyway to remove a comma, but only in the case where nothing would follow it. I have some data that can come in two varieties. The first:

Data, data

Is just fine the way it is.

The second type:

Data,

Is okay, but would look much neater if I could remove the superflous comma. The data length is variable and I am doing this in a much larger VBA code that will analyze quite a few cells on a worksheet.

Is this possible?

I have a list that I copied and paste into excel.

Its just column A and the extensions run together.... here is an ex

.c
.c00
.c01
.c01
.c02
.c2d
.c4
.c4d
.ca
.cab

What I need is a comma after each one so it will look like this.

.c,
.c00,
.c01,
.c01,
.c02,
.c2d,
.c4,
.c4d,
.ca,
.cab,

I have very long list of extensions and I need to have the comma after everyone so I can copy and paste it right into my php script

Thanks!

Hi,

Is that possible?

I have addresses in Excel cells, and need to insert a comma every 50 characters (max of 2 commas if possible).

eg.
ISTS Building E Mawson Lane 349 Gibson Road Maybury 9061
becomes
ISTS Building E Mawson Lane 349 Gibson Road Maybur,y 9061

I found some help on another board that suggested something like this for a similar issue, but I have no idea how to change that to suit me!

=LEFT(F4,50)&","&MID(F4,1,50)

which just keeps repeating the same 50 characters and deleting the rest.

Thanks,
Narelle