#1
|
||||
|
||||
Leading zero in field
Hi
Importing a csv file into Excel where one of the fields has a leading zero is an old problem. Excel treats the field as a number and removes the zero. There are several ways to fix this once you are in Excel - I favour putting in an apostrophe to force text instead of number format, for example - however, I'm trying to come up with a different solution. I have a colleague who works with some mail merge software which can only accept csv files and works in the same way as Excel, ie dropping the leading zero. I don't have access to their software, but I figured if I could get Excel to accept it, then their software would work as well. What I'm wondering is if there is any character I could put in at the start of the field which would force Excel to treat it as text, but which would not be visible when printed. It could appear as a space. I've tried adding ASCII 255 for example, which is a non-breaking space, thinking if it wasn't the usual ASCII 32 space it might encourage Excel to treat it as text, but no luck. Does anyone have any ideas on this one? Thanks for reading! |
#2
|
|||
|
|||
Start Excel (no file opened). Start Open File dialog, set file type to text or csv file, and open the file. You are given the opportunity to define data types for columns - set for this column the data type Text.
|
#3
|
||||
|
||||
Yeah, but that has to happen when his customer opens the workbook. The OP is looking for something he can put in that cell that'll handle the problem automatically, without the recipient having to do anything special.
I've never done this, but in at least the more recent versions of Excel I've seen an option that defines whether cell values in .csv files are store with quotes always, sometimes or never. I wonder whether setting that to Always (at your end) would result in the value "03" with quotes, and whether it would then read in as you want it at the other end? |
#4
|
||||
|
||||
If you don't have that quote option available (or if it doesn't work as I expect), you might try modifying the text output, either in a VBA or by some other method, to ~force~ quotes around that column. Maybe it would still work at the other end.
|
#5
|
||||
|
||||
Hi
Thanks for responses - sorry for my late reply. I was indeed hoping to add something to the text file to force it to work. Adding quotes just to the code field does nothing. I thought I'd cracked it when I found that adding a tab would actually work (it's a csv file remember) - the field came into Excel as text, with leading zero preserved. However, while this worked for Excel, it's sod's law that the other program I was trying to get a solution for still brought the field in as a number, and stripped the zero. So I offer this solution up to anyone who has this issue with excel, but I'll just have to keep looking for something else for my particular requirement. Cheers! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
alt + xxxx does not work in Onenote. The leading two bytes are ignored. | garyp | OneNote | 0 | 07-11-2018 07:10 AM |
How to remove trailing & leading spaces in a cell? | LearnerExcel | Excel Programming | 8 | 02-04-2018 08:22 PM |
Get rid of leading spaces before paragraphs | fig000 | Word | 11 | 03-14-2016 01:59 PM |
Numbering with fixed length having leading 0 | pmahesha | Word | 2 | 04-15-2015 11:48 PM |
Change the leading of a character style??? | Char t | Word | 1 | 11-05-2011 01:18 PM |