Zero starts of a number

[Guest]

I export a PO# from system database to excel spreadsheet, but the PO# starts
with zero, for example, 012345, when it exports to excel, the PO# becomes
12345 without "0", how can I do?
Thanks.

 

[Guest]

Hi Lisa,

Format as a text.




"Lisa" escreveu:

 

[Guest]

Marcelo:
Thank you. But the problem is I cannot set up excel spreadsheet before
exporting data, so when I export data to excel spreadsheet, even I change the
format to "TEXT", it doesn't work.
Do you know how to solve it?
Many thanks.

 

[Peo Sjoblom]

How do you export/import it? If you export to a file and the open that file
with excel you can import the file through data>import external data>import
data, select *.* under file types and browse to open it, then the text
import wizard will open and in step 3 you can select column data format and
text
Or if you can import to a txt file then excel will pop up the text import
wizard when you open the file

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Lisa...
If the data being imported to Excel is defined as a "text" string in the
exporting database then Excel will store the data as a "text" value... if
you can alter the export utility to include a single apostrophe in front of
the PO# so that the number coming into Excel looks like '012345 then Excel
will recognize this as a text string and not truncate the '0'...

If you need to change the value after it has been imported to Excel, you can
alter the string by using the following function: ="0"&TEXT(C2,"0") in an
adjacent column and then use Copy and Paste Special as Values to replace the
formula and eliminate the initial imported column...

Hope this helps -
Joe Mac

 

[Guest]

Format cell as CUSTOM =>00000000 where number of zeros is maximum number of
digits in number

HTH