Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
Hi.
There is a file that we want to export to a CSV file, but it is changing the format of things that look like numbers. It should export some of the numbers in a 5 digit format... 01000 , 01001 , etc. We tried changing the cell format to text, and using the formula =text(a1,"00000") and changing that to values... Either way, in the .CSV file, it still came out as 1000 , 1001 . For now, I did what was needed by creating a simple text file with the output. But, is there anyway to keep the leading 0 s on things that look like numbers, in a CSV file? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
Roll your own. Here is a start at Chip Pearson's site:
http://www.cpearson.com/excel/imptext.htm import/export text files -- Regards, Tom Ogilvy "mark" wrote in message ... Hi. There is a file that we want to export to a CSV file, but it is changing the format of things that look like numbers. It should export some of the numbers in a 5 digit format... 01000 , 01001 , etc. We tried changing the cell format to text, and using the formula =text(a1,"00000") and changing that to values... Either way, in the .CSV file, it still came out as 1000 , 1001 . For now, I did what was needed by creating a simple text file with the output. But, is there anyway to keep the leading 0 s on things that look like numbers, in a CSV file? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
which is basically what I mentioned that I already did.
thanks. "Tom Ogilvy" wrote: Roll your own. Here is a start at Chip Pearson's site: http://www.cpearson.com/excel/imptext.htm import/export text files |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
How did you verify that the leading 0's are lost?
Try opening that .csv file in Notepad to really check. mark wrote: Hi. There is a file that we want to export to a CSV file, but it is changing the format of things that look like numbers. It should export some of the numbers in a 5 digit format... 01000 , 01001 , etc. We tried changing the cell format to text, and using the formula =text(a1,"00000") and changing that to values... Either way, in the .CSV file, it still came out as 1000 , 1001 . For now, I did what was needed by creating a simple text file with the output. But, is there anyway to keep the leading 0 s on things that look like numbers, in a CSV file? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
For now, I did what was needed by creating a simple text file with the
output. doesn't really say that. For all I know, you typed it in by hand. Also, if you read it into excel, it will treat that column as numbers and you will again loose your leading zero. You can rename it to text and use the file open command to open the text import wizard an in the last frame, designate that column as text. If you record a macro to do that, then rename it to csv, your settings will be ignored. -- Regards, Tom Ogilvy "mark" wrote in message ... which is basically what I mentioned that I already did. thanks. "Tom Ogilvy" wrote: Roll your own. Here is a start at Chip Pearson's site: http://www.cpearson.com/excel/imptext.htm import/export text files |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
you very well may be right that the 0's exist in the .csv file, but as Tom
mentioned that they would, and we experienced, opening the .csv in Excel reformats them, producing the same problem. The problem is solved thought. Thanks. "Dave Peterson" wrote: How did you verify that the leading 0's are lost? Try opening that .csv file in Notepad to really check. mark wrote: Hi. There is a file that we want to export to a CSV file, but it is changing the format of things that look like numbers. It should export some of the numbers in a 5 digit format... 01000 , 01001 , etc. We tried changing the cell format to text, and using the formula =text(a1,"00000") and changing that to values... Either way, in the .CSV file, it still came out as 1000 , 1001 . For now, I did what was needed by creating a simple text file with the output. But, is there anyway to keep the leading 0 s on things that look like numbers, in a CSV file? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatted numbers to CSV problem
doesn't really say that. For all I know, you typed it in by hand.
point taken. It was a small number of columns with a middle sized number of rows, that my boss and someone else asked me to look at this morning when they ran into the formatting issue. I just strung it together in a text formula, and used a tiny code module to export it. In that instance, copying and pasting to Notepad would have worked about as well. Yep, I noticed the issues you mentioned about opening it in Excel and getting the same reformatting problem... and, I understand and could do what you're suggesting about using the Text import wizard (or coded version) and setting that column to Text. If the people that needed it this morning need it again, I'll do that. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numbers formatted as text | Excel Discussion (Misc queries) | |||
formatted numbers displayed as ##### | New Users to Excel | |||
Sorting formatted numbers | Excel Discussion (Misc queries) | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
importing large numbers to vb results in the numbers being formatted to scientific | Excel Programming |