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

Adding Comma Delimited Numbers In A Cell

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


Am having a problem. I need to add comma delimited numbers in a cell to return a value in another column this may sound straight forward however the comma delimited values have alternative values. for example 46 really = 3 8 really =7 how would i go about doing this. any help is appreciated.


View Answers     

Similar Excel Tutorials

Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
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 ...

Helpful Excel Macros

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
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel - UDF
- Return the highest number between two numbers that is in a range of cells with this UDF (user defined function) in Excel
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

Similar Topics

It might be a quite simple and trivial issue but I need some help in order to sort out this problem: I want to save some excel worksheets as txt files comma delimited.

I know that in principle it can be done very simply via the menu "File, save as csv (comma delimited)" BUT for some reasons I'm not explaining here I had to set up my default separators as semicolon ";".
Keeping in mind this limitation now I have the problem to generalise this macro so that it is saving every worksheets in my file as a txt file comma delimited

Sub export_txt()

For i = 1 To Worksheets.Count
ActiveWorkbook.SaveAs Filename:=Path & ActiveSheet.Name, FileFormat:=xlText
Next i
End Sub

Any help much appreciated!



When I paste several lines of comma delimited text into a worksheet
I get the icon to use the text import wizard. Then I have to select
delimited and comma everytime I paste.
How can I set that as the default for that worksheet?
As I paste entries several times it becomes rather annoying.
Any help would be appreciated.



I am using the macrorecorder to use text to column delimited comma, and then to "check off" the box comma delimited. However it doesn't work when I play the macro afterwards.

Hello all,

I regulary receive from a client a data transfer file that I must open in Excel to edit and then save as a Tab-delimited file for importing into a database. If a field has a comma in it, such as

123 45th St., Apt. 18

then Excel saves the field in the Tab-delimited file as:

"123 45th St., Apt. 18" (surrounded by quotes) This causes my database import process to error out.

The source file is comma-delimited. We have tried using a pipe | delimited file, but the same error still occurs. For reasons beyond my control, the client cannot feasibly save the source data as a tab-delimited file. Help?

The current procedu

- The client sends me the file as a comma-delimited .txt file in the format
"M","Smith ","Mary","J"," ","19591106"...etc for several dozen more columns
- I open the file in Excel via the Text Import Wizard, with a comma as the delimiter and all fields set to Text value
- I make the necessary edits
- I save the file as type Text (Tab Delimited)
- I then open the newly saved Tab Delimited file in Notepad to verify that everything is correct. All fields export correctly EXCEPT for those that contain a comma. They are export in quotes "texthere" as shown above.

How can I work around this problem? I have tried everything and can find no feasible solution.

We receive most of our files either in Excel or comma delimited. However this one file we receive as a bar delimited file, which we cannot import into our new system.

I can convert comma delimited files into Excel, but for the life of me cannot seem to find out how to even type a bar character.

Barring that (pardon the pun!), is there a way to convert the bar to a comma so that I could then convert Text to columns?

This may sound really simple to someone. I am trying to change an excel file to a comma delimited I have excel 2007 and i get a (?) appear between each object. Help, thanks

I am trying to export an excel document that I have created to a delimited
text file but it needs to be delimited by the character '|'. The only option
that I am getting is tab or comma delimited. Can anyone advise how I can do
either using Excel or is there a 3rd party software that can do this? All
help much appreciated.

Hello --

I am using Excel 2002 ( Office XL ) -- A recent contact I have made requires that I send a file in .CSV Tab Delimited Format.

I am only offered CSV Comma Delimited , CSV Macintosh and CSV DOS as Save As Options.

The Only Save As Tab Delimited option that I am offered is as "Text (Tab Delimited) "

Is there any way to accomplish this ?

Thanks as Always


I have an sql script that writes my database table data out to a file and i've given the file a name with the (.xls) suffix also, the data is delimited with a comma.

After running the script, i end up with a file in my directory and the file has the Excel ICON, but when i select on the ICON, Excel does not recognize that it's comma delimited of course and still requires that i import the data using the import utility.

I am quessing/assuming that there must be a setting or feature by now, that i can set that will tell Excel to assume that the data is in a comma delimited format and to automatically import the data using the comma delimited format?

Has anyone seen or heared of such an available setting before?

Thanks very much!

I have comma delimited data such as OX1024, OX2024, OY2024.

I'd like to split it based on the commas and trim the extra space.

Morning All

I'm looking for a formula option only for this query.

Excel 2010/2013

I need to extract a list of single items from a comma delimited string in Column A, each item is always separted by a comma except when there's only 1 item in that string and in that case it will just be the item name with no delimter.

I don't want to use text to columns as there's loads of manually faffing about

The attached file will explains what I need

Extract list comma delimited.xlsx



If I have the following data in two columns, I need VBA code that creates a new table that finds the data associated with A,B, or C and then lists it as comma delimited text --

It's kind of like a sumif in Excel except that instead of adding numerical values, I need to create a list of text.

Column One

Column Two
Text 1
Text 2
Text 3
Text 4
Text 5
Text 6

The result would look as follows:

A Text1, Text2, Text3
B Text4, Text5
C Text6

Any help you can give would be appreciated.

I am used to querying a comma-delimited text file using the connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Files\;Extended Properties=Text;HDR=Yes;

where the file is C:\Files\Example.csv

However, I now have a text file that is pipe-delimited ("|") instead of comma-delimited.
Do I need to specify anything more in my connection string or is ADO smart enough to realise the pipes are delimiting the data and the commas are not?

I use the Import Text Wizard to paste comma delimited text into an Excel
spread sheet. I end up with a single row of multiple columns. One column for
each piece of delimited text. What I really want is delimited text in a
single column of mutliple rows. One row for each piece of delimited text. How
can this be done?

Hi Everyone. I have a tab-delimited file that I open with Excel, make a few tweaks to, then save as a comma-delimited file. However, the export file has to use quotes as a text qualifier as well, and I can't seem to find an option to add the quotes automatically when saving as a CSV file. Does anyone know how to achieve this. I must admit it has been many years since I have used Lotus 1-2-3, but I seem to remember it being quite easy to do in that program.

Since quotes are used quite commonly as a text qualifier in a delimited file, I find it hard to believe that Excel would not support this feature, but I can't find it if it does.

I don't have to do this that often, but often enough that I would really like to find a solution that doesn't involve me opening the CSV file and manually adding the quotes where necessary.

Any help would be much appreciated.


Can anyone help me overcome the problem of excel putting quotes around lines containing commas when I save a file as a tab delimited text file.

e.g. This:

hello, mr excel
my name is xlkeuk
this line has no comma
this line, has a comma
as does, this one
this one does not


"hello, mr excel"
my name is xlkeuk
this line has no comma
"this line, has a comma"
"as does, this one"
this one does not

I want to kill the quotes around the lines and I want the text file to be the same. I have tried substitute(A1,",",char(130)) but that comma is different.

\tab is the RTF code for a TAB, does anyone know if there is a similar code for the comma? it's not \comma in case you were wondering.

Many thanks.

In my worksheet, I have a column, each line of which may contain one customer name or up to, say, eight. Each name is separated by a comma.

I want to construct a formula, array, or something which will automatically extract each customer name from the column containing all these comma delimited names and sort them elsewhere in the same worksheet, all vertically all together in one column.

Each name so sorted would have to pick up the related information in the other fields on the same line.

Any suggestions?

Hi friends,

I tried searching the forum first, honest, but "CSV" doesn't count as a search term.
I need to save a workbook as "csv" comma delimited (which excel 2007 does fine) but also quote delimited: less fine. I can set the options for text separation and text delimiting in OpenOffice... it surprises me that I can't do it in Excel!!! Any tips? I need to configure this for a non-power user, so command line utilities won't help much...


I have a CSV file that is pipe delimited. I saved the file as txt and am converting it into Excel 2003. I have 2 problems with the file. It is pipe delimited, but one column has a pipe in it and I need that column to NOT split. The other problem is that in any fields that have commas in them, it is replacing the comma with a weird symbol. Any help would be appreciated.

I have an excel spreadsheet that I need to export/convert to comma-delimited
format in order to import it into my accounting software. Can this be done?


My question is: Within a delimited text file, the data must be separeated by field/cell, and also by row, correct? How is the end of row marked out? I have a "^" delimited file which after opening in excel and separating by "^" gives me a huge singe row of data (it's not breaking according to the row in the orginal web based format.

Any ideas on this one?
Thanks in advance,


I'm trying to write a macro which will take a comma delimited list and (text to column, transpose) break them apart and paste/transpose them in cells which the macro inserts BASED on the number of items it counted. This is a very labor intensive worksheet and has lists of these that have to be broken out. Is this possible to do? And then I want to paste the contents to the right in ALL rows that were transposed. Then on to the next one.
Have I lost my mind?

I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?



I have attached a spreadsheet. On there is 2 worksheets called Masterproject and data.

In data i want to store a comma delimited list under column EmployeeList which will contain a list of the employees on the job.

What i wanted to do is loop through the list and then in masterproject i need to lookup each item in the list for the job and colour the the cell for the job next to the person.

So for example chris bates would be highlighted against the first job as he is assigned to it. This would then colour the cell

Please could someone assist