Re: convert number to text without losing zeroes
- From: DaveK <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Sep 2007 10:07:01 -0700
I am not opening the .csv file.. Excel never saves the leading zeroes in the
first place when saving to a .csv file, which is the problem, but they are
there when I import the data. When you do a data import using ODBC, you do
not get a text field, or a number field (although it right justifies it like
a number field), and you dont get the green triangle so it is not number
stored as text, which I thought was the case when I first posted.
I believe excel brings each item in as a custom format if it is all numbers,
and the values with characters it brings in as text, but it labels the whole
column as general (although if I create a custom field with 0000 and type in
0001 the value excel stores is 1, not 0001, where the data imported value
stored is 0001). Changing the format of the column does nothing. Anything
other than cut, copy, paste type actions on these number-like fields results
in Excel converting it to a number and dropping the zero.
I have been unable to duplicate what excel is doing to imported data by
manually entering data.. like you said, if you type 0001, you get 1, unless
you put a single quote in front in which case you get a number stored as
text, which for data purposes is not the same as a custom field (ie you
cannot use a lookup function to get that number).
If anyone is interested in examining some data, I would be happy to email a
sample to them of data imported with ODBC that has leading zeroes. Short of
manipulating each individual field I cannot find a way to save the zeroes
when saving to a .csv file.
"Dave Peterson" wrote:
Nobody said that the leading 0's would be saved if you opened a .csv file in.
But if I create a workbook and either enter the value as text '001 (or
preformatting the cell as text), or use a formula like: =text(a1,"000000") or
use a custom format of 000000, then those leading zeros are preserved when excel
saves the file as a .CSV file.
If you leave the cell's format as General and type in: 000123, then those
leading 0's will be lost as soon as you hit enter. Saving to a .CSV file will
not put them back. The data has to have the leading 0's for them to be saved in
the .CSV file.
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
"Pete_UK" <pashurst@xxxxxxxxxxx> wrote in message
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:
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
Hope this helps.
On Sep 25, 11:22 pm, DaveK <Da...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I cannot find an answer to this problem anywhere, perhaps someone here
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
the part. The part numbers are a mix of numbers, letters, and some
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
The problem is coming in when I have part numbers that start with zero.
example would be 010888. The zero gets dropped off when I save it as a
I figured I could convert it to text and it would be fine but when I
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
Please help.. I have some 9000 part numbers that I need to save to a .csv
and 300 of them start with zero.
- Re: convert number to text without losing zeroes
- From: Peo Sjoblom
- Re: convert number to text without losing zeroes
- Prev by Date: Re: Closing EXCEL only closes workbook - EXCEL '07
- Next by Date: Re: Help with copy/paste
- Previous by thread: Re: convert number to text without losing zeroes
- Next by thread: Re: convert number to text without losing zeroes