Adding Comma Delimited Numbers In A Cell
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Adding Comma Delimited Numbers In A Cell - Excel
|
View Answers
|
|
|
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
Lookup Adding: SUMPRODUCT & SUMIF
- See how to use the SUMPRODUCT & SUMIF functions together to Lookup multiple items and add them in one cell. This method is great when you have man ...
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.
The file has comma's within addresses, so opening it in Delimited by Comma won't work.
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.
