Re: convert number to text without losing zeroes

Tech-Archive recommends: Fix windows errors by optimizing your registry

That was the first thing I tried, and the preceding zeroes disappear. As I
was trying to get across in another reply, I cannot reproduce this situation
unless I am importing the data.. if you go to a general field and type 0123,
you get 123. If you type '0123 you get 0123 with the green triangle. If you
change the format to custom, you set the number of characters and can get
0123. I think what happens on import is excel takes any field with all
numbers and counts the digits and creates a custom field format for that
number. When you try to convert it to anything else, it drops the zero.

I just don't think excel is capable of taking a column of data with a mix of
these custom formats and doing anything without losing the preceding zeroes.
However, Access does not have the field restrictions that excel has, so it
worked for me.

Thanks again for any advice here.. I learned a lot about what excel's
limitations are in the last couple days.

"Ron Coderre" wrote:

Sorry, I misunderstood your situation.

Perhaps this approach will work for you....

Make sure the Part Numbers not only *look* like text, but that the cells are
actually formatted as text.

Select the column of Part Numbers
<data><text-to-columns>...Click [Next] until Step_3_of_3
Check: Text (to set the column type)....Click [Finish]

Now try saving the file as a CSV

Does that help?
If no...can you post a sample file at one of the free file-hosting sites and
post the link for us?
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)



"DaveK" <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F3AC711A-8594-4F1B-A86E-F603C02EA938@xxxxxxxxxxxxxxxx
Thanks for the quick response! Unfortunately, the problem lies in excel
converting the "number stored as text" (ie 010888) to 10888 when it writes
it
to a .csv. I am using the .csv to populate data in an SQL server. When I
pull the data into excel, it is in the correct format. If I use any of
the
tricks I have found to convert the numbers to text, excel drops the
preceding
zeroes. If I save the files as .csv it drops the preceding zeroes. I
don't
know what excel has against preceding zeroes, but it sure doesnt like to
keep
them in my data. I really hope someone has a solution for this..

Dave


"Ron Coderre" wrote:

One of the usual "fixes" is to change the file extension on the CSV file
to
..TXT.

Then, when you open the file, Excel's Text Import Wizard will allow you
to
set the delimiter to Comma and set the first column to be Text...which
will
preserve the leading zeros.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"DaveK" <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:157ED86E-1DA3-46D7-A624-5A7B4D866D74@xxxxxxxxxxxxxxxx
I cannot find an answer to this problem anywhere, perhaps someone here
can
help.
I have 2 columns of data that I pulled from my proprietary database
using
ODBC. The first column is part numbers, and the second is a list price
for
the part. The part numbers are a mix of numbers, letters, and some
dashes.
I am saving this list to a .csv, uploading it to my web server, and
then
logging in to my webserver and importing the data from the .csv to a
mysql
database.
The problem is coming in when I have part numbers that start with zero.
An
example would be 010888. The zero gets dropped off when I save it as a
.csv.
I figured I could convert it to text and it would be fine but when I
convert
it to text in excel it drops the zero too. A part number like 010888L
is
fine because its treated like text and the zero remains through any
conversion.

Please help.. I have some 9000 part numbers that I need to save to a
.csv
and 300 of them start with zero.






.

Relevant Pages

  • Re: convert number to text without losing zeroes
    ... I am not opening the .csv file.. ... I believe excel brings each item in as a custom format if it is all numbers, ... in Excel converting it to a number and dropping the zero. ...
    (microsoft.public.excel.misc)
  • Re: convert number to text without losing zeroes
    ... use a custom format of 000000, then those leading zeros are preserved when excel ... saves the file as a .CSV file. ... treated as a number but retains the zero. ...
    (microsoft.public.excel.misc)
  • Re: convert number to text without losing zeroes
    ... Now try saving the file as a CSV ... pull the data into excel, it is in the correct format. ... If I save the files as .csv it drops the preceding zeroes. ... The problem is coming in when I have part numbers that start with zero. ...
    (microsoft.public.excel.misc)
  • Re: convert number to text without losing zeroes
    ... I've never seen excel drop the leading 0's when saving as a .csv file in any ... You may want to try saving the .csv file and then check using notepad once more. ... always drops the zero. ...
    (microsoft.public.excel.misc)
  • Re: convert number to text without losing zeroes
    ... Look at the CSV with Notepad. ... The problem comes if you try to use Excel to read the CSV, ... If I save the files as .csv it drops the preceding zeroes. ... The problem is coming in when I have part numbers that start with zero. ...
    (microsoft.public.excel.misc)