Dropping Zeros, But In A Formula?

[GGeck68]

Hello Everyone,

I know from time to time we have all dealt with excel issues involving the program dropping leading zeros, but this case is a first for me.

In order to monitor account numbers in a spreadsheet, I would like to setup a formula as follows:

=IF(OR(A1=TR002,A1=TR050),"Yes","No"))

However upon hitting ENTER, excel automatically drops the zeros from the formula to read:

=IF(OR(A1=TR002,A1=TR050),"Yes","No"))

Unfortunately TROO2 and TR050 in this example cannot be changed as they are static account numbers.

If anyone has ideas on how I can correct this, I would be extremely grateful!

 

[sous2817]

Weird. In 2007 it trims out the zeros. If I load 2007 in compatibility mode, the TR005 doesn't drop the zeros (it gives me a #NAME? error). If I "upgrade" the sheet to 2007 I get the following warning: "The name, TR005, either conflicts with a valid range reference or is invalid for Excel". This is using the formula =TR005 in A1.

I don't have 2003 on my work machine, but I'm going to stand by my guess that Excel is interpreting TR005 as a cell reference and that is why it's dropping the 00(zero zero) from your formula.

 

[GGeck68]

I think you are exactly right sous, how weird!