Numbers as text in excel

  • Thread starter Thread starter Keyed4U
  • Start date Start date
K

Keyed4U

I have a file that is created via an online shopping cart, it saves the file as
an excel (.xls) file and does so just nicely. The problem is in the numbers.
Rather than converting a text file to excel and formatting everything as text,
I have to have the part numbers formatted as they are in a text file. All
leading zeros must stay intact.

Example:

txt file:

0012345789 This is the part desc. 10.99 1(qty) 10.99
1758961233 This is another desc 1.00 3 3.00
0983201214 This is yet another 100.89 1 100.89

in the xls file it auto-formats as this:

12345789 This is the part desc. 10.99 1(qty) 10.99
1758961233 This is another desc 1 3 3
983201214 This is yet another 100.89 1 100.89

The problem is that 00123456789 and 123456789 might be 2 different items in the
database, and for the number to be missing any relevant leading zeros would
cause the system to order the wrong part.

The question....Is there a way to designate in the file, as it is being
created, that the numbers be treated as text regardless?

Thanks.

Jeff
 
If you are getting a text file and are opening it in Excel yourself, the
Text Import Wizard should open up (as long as the file does not have a CSV
extension).

The third screen of the wizard lets you set the number format of each data
column. You should pick Text for any numeric columns where you want leading
zeros preserved.
 
Thanks, I understand that and am very fluent in excel. The problem is the file
is a text file saved as an excel file via the web server. I was wondering if
anyone knew a way to tell the field that it is text and not a number in that
file. Before it is opened in excel. That way I can avoid having to run the
wizard and remembering to select text all the time.

Jeff
 
I was wondering if anyone knew a way to tell the field that it is text

There is no marker you can put in a file to tell Excel how to handle a
field. I'm unclear how the web server is making the Excel file. Is it
automating Excel or does it have the ability to construct an XLS on its own?
Either way it could be modified to do what you need I would think. But if
you don't control the web server then that's out.
text all the time.

This implies your getting a text file not an XLS. Maybe you're getting
both. If the text file format is standard you could use a macro to open it
and you wouldn't have to go through the wizard.
 
I didn't even think of setting up a macro. But yes I get both a text file and
an excel file. They both have the same name all that is different is the
extension, ie: 1095642.txt and 1095642.xls

Thanks for the reminder about macros!!I think I will try one and see if I can
get it to work right. The less I have to worry about the faster and smoother
the order system will flow!!

Jeff
 
Back
Top