Re: convert number to text without losing zeroes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It is excel 2003.

Keep in mind this is NOT when you have a number, or a text field, it only
happens when you import data. It is not a true text field (if it was the
stupid green triangle would appear) and it is not a number field. It is
treated as a number but retains the zero. The data is fine until you try to
manipulate it in excel. The only way I found to convert it to true text
without dropping the preceding zero is to do a function =text(a1, "000000")
but that only works if the number has 6 digits, and my data ranged from 3 or
4 digits to maybe 19..

If you want to reproduce this, import data from some source that contains
some numbers and some text in the same column. The numbers will right
justify, and anything with a character in it will left justify.. now save it
to a .csv and open the .csv with notepad.. you will see there are no
preceding zeroes now. If you enter a number manually, it will drop the
preceding zeroes. If you put a single quote in front, it will give you the
green triangle which denotes number stored as text.

Using access to import my data and exporting it to a .csv is working great
and actually works better than excel did.. I should have started with access
to begin with. I use excel perhaps 10 times a day to import data and create
a report so I am very comfortable with it and it is very easy. In older
versions of excel, typing a number in a field that had preceding zeroes
removed the zeroes, and even worse, if the number had 6 digits it would
automatically convert it to a date, so at one time I had hundreds of
spreadsheets with part numbers that the author had to put a single quote in
front of to force it to store the number as text. When I started linking all
these sheets to a mater spreadsheet with all my pricing (this was for our
product catalog), I found that a number stored as text will not lookup from a
regular number that is the same, or from a number imported from an external
source (ie 010888), so back then I had to learn how to convert a number
stored as text to a real number. However, I was never exporting that to a
..csv until now.

Excel needs to add to their import data function and have a raw data field
format where everyting coming in is true text, instead of trying to be smart
and make certain fields numbers just because they dont have characters in
them. Further, there should be a way to define each column when importing
data, much like you can when opening a .txt file.

One more note, I use the .csv data to populate a sql database in a remote
server that does not allow a direct import. I do not open it back up in
excel to check things out and didnt notice this problem until I was trying to
query my sql data with a part number starting in zero.

"David Biddulph" wrote:

Your advice to look at the csv with Notepad is sensible, as is the adviceto
rename the csv as txt to control the reimport.

In which version of Excel are you encountering the situation you describe
whereby numbers stored as numbers and formatted with a leading zero will
lose the leading zero on saving as CSV, Pete? That doesn't happen for me
with Excel 2003
--
David Biddulph

"Pete_UK" <pashurst@xxxxxxxxxxx> wrote in message
news:1190759935.296359.70430@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Just check that the leadings zeros are not actually missing from
the .csv file by opening it in Notepad - you should be able to see the
format of the file more clearly there. If the part numbers are stored
in Excel as numbers and just formatted to have a leading zero, then
they will be missing from the csv file - a formula like:

=TEXT(A1,"000000")

will convert them to text values. You could fix these values then copy
them back over the offending cells.

If you are bringing the file into Excel then rename it by changing
the .csv extension to .txt. Then with Excel running do File | Open,
point to the .txt file and then Excel will take you into the Data
Import Wizard, where you have more control over how the fields are
treated.

Hope this helps.

Pete

On Sep 25, 11:22 pm, DaveK <Da...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.





.

Quantcast