Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Adding Comma Delimited Numbers In A Cell

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

Hello,

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.

Regards
Dan


Similar Excel Video Tutorials

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
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
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof
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
Worksheets(i).Activate
ActiveWorkbook.SaveAs Filename:=Path & ActiveSheet.Name, FileFormat:=xlText
Next i
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Any help much appreciated!

thanks

max


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.

Carol





Hi

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


Hello

I am presently using excel to record data posted from an external source (excuse the lack of vocabulary newbie). To be specific it is a card program. The table that the card values are sent to in excel is very simply constructed and appears like this

Cards
Player 1 1, 23, 14
Player 2 2, 7, 25

Thus all three cards are in the same column and delimited by a comma. Now i am aware normally if i wanted to separate these three values into separate columns I would use the text to column fuction this I have tried with no success. I would like these numbers to have their own columns but the problem is that not all the numbers appear at once only one value every round and at the end of each game it is cleared. Is it possible to separate these numbers? any help will be greatly appreciated.

Regards
Dan


If I have values in cells A1:G1, what is the fastest way to convert them to a comma-delimited array and insert them into cell C1? So the result I'd be looking for, for example, is: 8, 5, 7, 6, 9, 1, 7.

Then, how would I take the array and then convert it into a row of values OR a column of values?

Thank you


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


Hello,

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


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.


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
A
A
A
B
B
C

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.

Alan


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

becomes:

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


Problem:
I have a list of 1000+ names. Some are in conventional format eg: "Joe Bloggs" while others are inverted / comma delimited eg: "Bloggs, Joe".

Previously, I have got round this by converting text to columns (cell A to B & C) and then combining eg C1&" "&B1

Would it be possible to automate this using a macro that doesn't split the column? I was thinking something along the lines ...

For all cells in range
If cell.value contains ", " then
RHS & " " & LHS
End if
Next

Obviously the above is nonsence code but that is my line of thinking


how do you view a comma delimited file vertically instead of horizontally after import


Greetings -

I see how to import a comma delimited txt file into Excel.

I need to go the other way -

Can I go from Excel and produce a comman delimited text file?

Thanks
Sparky_


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

Peace
nz


Hello:

Im a web programmer that never used excel.

I have a new customer who is an accountant and who with excel he designed himself an accounting processing application.

He wants me to make an online tool to capture data he needs from his customers and this data I will be supplying to him in a comma delimited file.

He now needs a way to migrate the data from my file into his excel accounting application.

The data in my comma delimited file is saved in columns that matches his columns in his application. He currently is having his secretary copy/paste every single item in every column and in every row of my file by hand, and entering it in every column and row of his application which is ridiculous.

Im sure there may be an easier way to migrate this data

If you can help ill appreciate it.

virtualweb


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.