[Hidden-tech] Lead Zero issue in Excel

[Don Lesser]

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)

 <mailto:dlesser at ptraining.com> dlesser at ptraining.com

 <http://www.ptraining.com/> 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/8c145087/attachment.html