Dropping Zeros, But In A Formula?

GGeck68

New Member
Joined
Jan 22, 2009
Messages
6
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.:eek:
 
Upvote 0
My apologies, the formula is actually changes to:

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

Sorry about the mis-type!

Any ideas?
 
Upvote 0
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.:)
 
Upvote 0
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!!!
 
Upvote 0
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.:eek:
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top