[Hidden-tech] Lead Zero issue in Excel

Jeff Brand jeff at deltafactory.com
Fri Jun 24 09:04:37 EDT 2016


I don't think the ' trick will work in the original CSV. When importing 
from that format, Excel doesn't treat any of the values as formulas and 
would just treat it as the literal string, for example: '01234

You could apply a custom number format to the column of "00000" to force 
5-digits to appear. Non numeric values (hyphenated ZIPs, Canadian postal 
codes, etc.) are unaffected by that rule.

On 6/23/2016 4:33 PM, Don Lesser wrote:
> What Doug said. Can you concatenate a ' in front of the zip code 
>  before you download it? Using the zip code format in Excel is not a 
> solution since is only displays the leading zero without changing the 
> actual number.
>
> You could write a macro in Excel that does this, and any other 
> formatting you need, but you'd need to install it in every user's 
> PERSONAL.XLSB leading to other issues.
>
> Excel has done the conversion at least since 2003.
>
> Sent from my iPad
>
> On Jun 23, 2016, at 4:08 PM, Doug Lowing <delowing at gmail.com 
> <mailto:delowing at gmail.com>> wrote:
>
>> Excel is very persistent trying to drop lead zeros from numbers-as-text.
>>
>> If you are allowed to add data to the CSV file, you can precede each 
>> number with a single quote '  Excel understands anything following is 
>> a string and does not show the quote.
>>
>> If you simply open the csv with excel, tough nuts. Excel will always 
>> drop the lead zero because it treats csv like they were native excel 
>> files.
>>
>> If you rename the csv file to txt (example: fred.csv becomes 
>> fred.txt), then you can open the text file and Excel asks several 
>> questions to force the number to be recognized as a string
>>
>> -Doug Lowing
>> scarred-for-life working with Excel as data source
>>
>>
>> On Thu, Jun 23, 2016 at 3:33 PM, Al Canali <al.canali5 at gmail.com 
>> <mailto:al.canali5 at gmail.com>> wrote:
>>
>>     I have data that downloads from a PHP application to a csv file.
>>     It was flawlessly converting to excel with the lead zero in zip
>>     codes (01576). The csv file has the zeros in it. The zeros no
>>     longer show in the excel file. Creator of the php file is not
>>     available. About the only thing different is the server and the
>>     fact that the file lay dormant for about a month on the new server.
>>
>>     Here's what I know and have implemented: field is set up as a
>>     text field  -  I know how to convert the field column in excel to
>>     make the zero appear. This is not viable for this client. It must
>>     flow into the excel file without any further steps.
>>
>>     Anyone have any tricks up their sleeve to make this happen??
>>     -- 
>>     Al Canali - Canali Designs
>>     www.canalidesigns.com <http://www.canalidesigns.com>
>>     413-337-4740 <tel:413-337-4740>
>>
>>     _______________________________________________
>>     Hidden-discuss mailing list - home page: http://www.hidden-tech.net
>>     Hidden-discuss at lists.hidden-tech.net
>>     <mailto:Hidden-discuss at lists.hidden-tech.net>
>>
>>     You are receiving this because you are on the Hidden-Tech
>>     Discussion list.
>>     If you would like to change your list preferences, Go to the Members
>>     page on the Hidden Tech Web site.
>>     http://www.hidden-tech.net/members
>>
>>
>> _______________________________________________
>> Hidden-discuss mailing list - home page: http://www.hidden-tech.net
>> Hidden-discuss at lists.hidden-tech.net 
>> <mailto:Hidden-discuss at lists.hidden-tech.net>
>>
>> You are receiving this because you are on the Hidden-Tech Discussion 
>> list.
>> If you would like to change your list preferences, Go to the Members
>> page on the Hidden Tech Web site.
>> http://www.hidden-tech.net/members
>
>
> _______________________________________________
> Hidden-discuss mailing list - home page: http://www.hidden-tech.net
> Hidden-discuss at lists.hidden-tech.net
>
> You are receiving this because you are on the Hidden-Tech Discussion list.
> If you would like to change your list preferences, Go to the Members
> page on the Hidden Tech Web site.
> http://www.hidden-tech.net/members

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.hidden-tech.net/pipermail/hidden-discuss/attachments/20160624/92b72872/attachment.html 


Google

More information about the Hidden-discuss mailing list