Turning off auto formatting

[Guest]

I am using several excel sheet to report on a rather large PO system. Many
customers have preceding zeros on their POs, while others do not, and being
that different customers have several different numbering systems, I cant
just arbitrarily format all cells a certain way. So,to avoid further
headaches, can I diable the built in formatting that wants to delete
preceding zeros? If so, can someone tell me how this is done please.
Thankyou.

 

[Guest]

The main problem is that Excel will convert numeric data into numbers unless
it knows that they should be treated as text. The solution to this problem
depends on how the data is getting into your spreadsheet. If you are copying
and pasting the data into the spreadsheet, you can (prior to pasting), format
the area that you are pasting to as text and Excel will not remove the
leading zeros while it pastes the data. If you are loading text files the
import wizard gives you the option to specify the data type for each column
of data you are importing.

Another problem is that it sounds as if the data you already have in your
Excel sheet has been converted to numbers, and the leading zeros are already
gone. You will have to fix these or reload them as text.

HTH,

TK

 

[Guest]

Even when I manually type in a leading zero, unless I put an ' in front (cant
convert it to text, because everytime I try, my lookups stop working, which
is another issue entirely) the zero disapears again. So aside from an ' or
changing the cell from numeric to text, there is no way to make it stop
deleting zeros that I just manually input?

Also, how this data is gotten is interesting, I am using Crystal Reports to
pull this data out of SQL, the export routine converts the data to Excel 7,
then I migrate these daily reports into my working files which are in Excel
2003. I wasn't sure if that would matter, but I figured the extra info
wouldn't hurt.
Thanks

 

[Guest]

The leading zeros drop off because a numeric format is being used and Excel
treats your entry as a number, not text. This is correct formatting for
numbers. Only text can have leading zeros, and unless you save the PO codes
as text (by preceding them with apostrophes or by formatting the range as
text), you will have to live with the missing leading zeros. One other option
may be to use formulas to pad all the PO codes with zeros out to a specified
length, but you seemed to dismiss this oution in your original post. If you
wanted to go this route a formula like:

=TEXT(A1,"0000000000")

would pad any number (or text that looks like a number) with zeros up to a
10 character code.

By the way, your lookups should work properly if everything is either text
or numeric, but they will not work if you're trying to lookup a numeric code
by matching it with a text string or vice versa. Remember that a text value
that looks like a number is not equal to the number it 'looks like.'

Hope that helps,

TK