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!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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:
 

GGeck68

New Member
Joined
Jan 22, 2009
Messages
6
My apologies, the formula is actually changes to:

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

Sorry about the mis-type!

Any ideas?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Are they named ranges?
 

GGeck68

New Member
Joined
Jan 22, 2009
Messages
6
No Norie, these are not named ranges.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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:
 

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
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

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

Forum statistics

Threads
1,082,017
Messages
5,362,695
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top