Leading zeros

When entering an 'IF' formula to locate information on another spreadsheet
the formula truncates the leading zeros in my argument. The information I
want is tied to a reference with leading zeros i.e. 00123 and so on. How can
i set the formula to accept the leading zeros to locate the correct
information?

Maybe you can use "00123" in your formula. =if(a1="00123", ...

Or if 123 is in a cell, =if(text(a1,"00000")....

But if that reference is just 123 and formatted to show leading 0's, you'll
still want to use the numeric value 123--not the text "00123".



Paul wrote:
>
> When entering an 'IF' formula to locate information on another spreadsheet
> the formula truncates the leading zeros in my argument. The information I
> want is tied to a reference with leading zeros i.e. 00123 and so on. How can
> i set the formula to accept the leading zeros to locate the correct
> information?

--

Dave Peterson

My formula is as shown - =IF('[production sheet
template.xls]1'!$Q$14=212,'[production sheet template.xls]1'!$S$14,0) - My
problem is that I need to use 00212 instead of 212 in the formula but when i
use 00212 excel truncates it to the above and the correct value is then not
resolved in the cell. I am ok with formatting the cell itself to show the
correct value, its the formula itself that truncates and then does not use
the leading zeros. Can you help please?

"Dave Peterson" wrote:

> Maybe you can use "00123" in your formula. =if(a1="00123", ...
>
> Or if 123 is in a cell, =if(text(a1,"00000")....
>
> But if that reference is just 123 and formatted to show leading 0's, you'll
> still want to use the numeric value 123--not the text "00123".
>
>
>
> Paul wrote:
> >
> > When entering an 'IF' formula to locate information on another spreadsheet
> > the formula truncates the leading zeros in my argument. The information I
> > want is tied to a reference with leading zeros i.e. 00123 and so on. How can
> > i set the formula to accept the leading zeros to locate the correct
> > information?
>
> --
>
> Dave Peterson
>

I run into this sort of an issue at work from time to time. What you
basically have to do is tell your formula to ignore the first couple of zeros
when checking the value of your cell. You can do this by using the MID
function. This function checks only a portion of the cell for a value
instead of looking at the whole.

For example, the syntax for the function could look like this: MID(A1,3,10)
where A1 is your cell reference, 3 is how many characters from the left to
start looking, and 10 is the maximum number of characters to look for,
although this number can be any length you choose.

How this translates to your problem is this: Let's say your value is in
cell A1 and it equals '00212', the function will examine this value starting
at the third character and every character afterward until it reaches 10
characters. So the value will end up being '212' not '00212'.

Try entering this forumula: IF('[production sheet
template.xls]1'!mid($Q$14,3,10)="212",'[production sheet
template.xls]1'!$S$14,0)

That should do the trick. Just make sure that you put the 212 in quotation
marks as shown above. Otherwise, I don't think it will work.


"Paul" wrote:

> My formula is as shown - =IF('[production sheet
> template.xls]1'!$Q$14=212,'[production sheet template.xls]1'!$S$14,0) - My
> problem is that I need to use 00212 instead of 212 in the formula but when i
> use 00212 excel truncates it to the above and the correct value is then not
> resolved in the cell. I am ok with formatting the cell itself to show the
> correct value, its the formula itself that truncates and then does not use
> the leading zeros. Can you help please?
>
> "Dave Peterson" wrote:
>
> > Maybe you can use "00123" in your formula. =if(a1="00123", ...
> >
> > Or if 123 is in a cell, =if(text(a1,"00000")....
> >
> > But if that reference is just 123 and formatted to show leading 0's, you'll
> > still want to use the numeric value 123--not the text "00123".
> >
> >
> >
> > Paul wrote:
> > >
> > > When entering an 'IF' formula to locate information on another spreadsheet
> > > the formula truncates the leading zeros in my argument. The information I
> > > want is tied to a reference with leading zeros i.e. 00123 and so on. How can
> > > i set the formula to accept the leading zeros to locate the correct
> > > information?
> >
> > --
> >
> > Dave Peterson
> >

You could try:

=IF('[production sheet template.xls]1'!$Q$14="00212",'[produc......

But this means that Q14 holds a text string--not just 212 with a custom format
of 00000.



Paul wrote:
>
> My formula is as shown - =IF('[production sheet
> template.xls]1'!$Q$14=212,'[production sheet template.xls]1'!$S$14,0) - My
> problem is that I need to use 00212 instead of 212 in the formula but when i
> use 00212 excel truncates it to the above and the correct value is then not
> resolved in the cell. I am ok with formatting the cell itself to show the
> correct value, its the formula itself that truncates and then does not use
> the leading zeros. Can you help please?
>
> "Dave Peterson" wrote:
>
> > Maybe you can use "00123" in your formula. =if(a1="00123", ...
> >
> > Or if 123 is in a cell, =if(text(a1,"00000")....
> >
> > But if that reference is just 123 and formatted to show leading 0's, you'll
> > still want to use the numeric value 123--not the text "00123".
> >
> >
> >
> > Paul wrote:
> > >
> > > When entering an 'IF' formula to locate information on another spreadsheet
> > > the formula truncates the leading zeros in my argument. The information I
> > > want is tied to a reference with leading zeros i.e. 00123 and so on. How can
> > > i set the formula to accept the leading zeros to locate the correct
> > > information?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson