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!

 

[Norie]

Don't see any zeroes being dropped there.

Are TR002 and TR050 named ranges?

If they are not the formula would produce a #NAME! error.

 

[GGeck68]

My apologies, the formula is actually changes to:

=IF(OR(A1=TR2,A1=TR50),"Yes","No"))

Sorry about the mis-type!

Any ideas?

 

[Norie]

Are they named ranges?

 

[GGeck68]

No Norie, these are not named ranges.

 

[Norie]

Well for a start they should be in quotes, they do appear to be text rather than numbers after all.

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

And if they are text I see no way that Excel would be removing the 0.

Without quotes it will be considering them as named ranges and if they don't exist you'll get the error I mentioned previously.

 

[GGeck68]

Hello Norie,

When I add the quotes, the formula maintains the zeros and works perfectly well for me. Thanks very, very much for your help!!!

 

[Norie]

No problem.

But I'm still intrigued at the behaviour, perhaps we'll never no the answer but it's probably out there - probably somewhere on Google.

 

[sous2817]

Are you using Excel 2007? If so, TR is a valid column range, so it's interpreting the 0 (zeros) in your TR as unnecessary and trimming them for you.

That's my guess anyway...

 

[GGeck68]

I am using 2003 sous. Without the quotes, it is definitely trimming the formula. I have googled this thing like crazy and can't find any explanation.