[Hidden-tech] Lead Zero issue in Excel

[Jeff Brand]

Ah, I missed the part where Al said he knows about formatting, but also 
needs there to be no additional steps.

Two more thoughts:
1. Assuming this is part of a larger workflow, handle the formatting in 
the next step.
2. Replace the CSV creation with a native Excel file generated 
programmatically. Any of the suggested manual fixes can be implemented 
as the file is created. (Send PHP developer here: 
https://github.com/pear/Spreadsheet_Excel_Writer)


On 6/24/2016 9:40 AM, Don Lesser wrote:
>
> The ‘01002 comes in as ‘01002 but if you edit it then press Enter, it 
> is read properly.
>
> The custom number format will display the correct value (01002) but 
> the underlying number will not change (1002). This could be a problem, 
> depending on the use of the file.
>
> The proper formula for this conversion is below. Assuming the zipccode 
> is in column F, put the formula in the first row and copy it down.
>
> =text(F2,”00000”)
>
> Another thought:
>
> 1.Rename the file to a .txt extension.
>
> 2.When you import, the wizard will take over.
>
> 3.Screen 1 – specify delimited.
>
> 4.Screen 2 – specify comma
>
> 5.Screen 3 – select the zip code column and specify Text format.
>
> 6.The zip code will come in as text with the leading zero intact.
>
> This is probably far too much for your clients. Again, some sort of 
> import macro can automate this for the client, but I can’t find 
> another way to go. I will keep trying.
>
> Don Lesser
>
> Pioneer Training, Inc.
>
> 139B Damon Road, Ste 8
>
> Northampton, MA 01060
>
> (413) 387-1040
>
> (413) 586-0545 (fax)
>
> dlesser at ptraining.com <mailto:dlesser at ptraining.com>
>
> www.ptraining.com <http://www.ptraining.com/>
>
> *From:*Jeff Brand [mailto:jeff at deltafactory.com]
> *Sent:* Friday, June 24, 2016 9:05 AM
> *To:* Don Lesser <dlesser at ptraining.com>; HT-discuss 
> <hidden-discuss at lists.hidden-tech.net>
> *Subject:* Re: [Hidden-tech] Lead Zero issue in Excel
>
> 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
>     <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
>

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