Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numeric values as character in CSV
Whenever I populate a numeric value into a cell, the leading zeros are always
removed even when I create a custom format. Excel thinks its a number but I want to treate it as a character because I have to FTP the file to a mainframe. I always get the warning that it contains some features not compatible with CSV Example, when I put 012345 in a cell, when I save it and then re-open it, it contains 12345. I need it to be 012345 because the machine I am FTP'ing the file too has this column formatted as *char 6, so I need it to end up as '012345' not '12345 '. I can't save it as .xls because I can't FTP an .xls file to another box and and use it in another application. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numeric values as character in CSV
You will need to convert the cell to text to get it to stay the way you want
in the CSV file. Either use an initial apostrophe in data entry (as in '012345). This forces the entry to stay as text. If it's a calculated field, use something like: =text(a1,"000000") Again, this will create a 6-character text field. Regards, Fred. "GKW in GA" wrote in message ... Whenever I populate a numeric value into a cell, the leading zeros are always removed even when I create a custom format. Excel thinks its a number but I want to treate it as a character because I have to FTP the file to a mainframe. I always get the warning that it contains some features not compatible with CSV Example, when I put 012345 in a cell, when I save it and then re-open it, it contains 12345. I need it to be 012345 because the machine I am FTP'ing the file too has this column formatted as *char 6, so I need it to end up as '012345' not '12345 '. I can't save it as .xls because I can't FTP an .xls file to another box and and use it in another application. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numeric values as character in CSV
That doesn't work, for CSV anyway. When you save the CSV file you get the
standard prompt that says it contains features not compatible with CSV. The choices are YES, to save it as CSV omitting the incompatible features or NO to save it in another format besides CSV (like .xls). If you select YES, then when you return, the leading zeros have been removed. What you said works for .xls but not .csv. I tried it before I ever created this post. Maybe I have an option set wrong or something. "Fred Smith" wrote: You will need to convert the cell to text to get it to stay the way you want in the CSV file. Either use an initial apostrophe in data entry (as in '012345). This forces the entry to stay as text. If it's a calculated field, use something like: =text(a1,"000000") Again, this will create a 6-character text field. Regards, Fred. "GKW in GA" wrote in message ... Whenever I populate a numeric value into a cell, the leading zeros are always removed even when I create a custom format. Excel thinks its a number but I want to treate it as a character because I have to FTP the file to a mainframe. I always get the warning that it contains some features not compatible with CSV Example, when I put 012345 in a cell, when I save it and then re-open it, it contains 12345. I need it to be 012345 because the machine I am FTP'ing the file too has this column formatted as *char 6, so I need it to end up as '012345' not '12345 '. I can't save it as .xls because I can't FTP an .xls file to another box and and use it in another application. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numeric values as character in CSV
Don't re-open the *.csv file in Excel.
Open it in Notepad or similar. Your zeros will be there. Gord Dibben MS Excel MVP On Thu, 22 May 2008 16:58:00 -0700, GKW in GA wrote: That doesn't work, for CSV anyway. When you save the CSV file you get the standard prompt that says it contains features not compatible with CSV. The choices are YES, to save it as CSV omitting the incompatible features or NO to save it in another format besides CSV (like .xls). If you select YES, then when you return, the leading zeros have been removed. What you said works for .xls but not .csv. I tried it before I ever created this post. Maybe I have an option set wrong or something. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I convert numeric value to its character value ex. 10=Ten | Excel Worksheet Functions | |||
PICTURE RECOGNITION AS A ALPHA-NUMERIC CHARACTER | Excel Worksheet Functions | |||
PICTURE RECOGNITION AS A ALPHA-NUMERIC CHARACTER | Excel Worksheet Functions | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
show character value of a month rather than numeric? | New Users to Excel |