Left Formula Read a Zero if it in front of a Number

[xchoco]

hello exceller, I have a question about reading a zero number in a cell
here an axample :
on Cell A1 = 01
on Cell B2 = 10
so I write at C1 the formula is =left(A1,1) but how come the number that comes out is 1 not 0 (zero) ?
but if I write at D1 the formula is = right(A1,1) the number comes out is 0 (zero)
how to make the formula that can read the zero in Cell C1 ?
can someone help me with this ?
thankss

 

[T. Valko]

hello exceller, I have a question about reading a zero number in a cell
here an axample :
on Cell A1 = 01
on Cell B2 = 10
so I write at C1 the formula is =left(A1,1) but how come the number that comes out is 1 not 0 (zero) ?
but if I write at D1 the formula is = right(A1,1) the number comes out is 0 (zero)
how to make the formula that can read the zero in Cell C1 ?
can someone help me with this ?
thankss

Excel doesn't "see" leading zeros in numeric numbers. The leading zero is just for display purposes.

You would have to format the cell as TEXT for Excel to "see" the leading zero.

 

[pplstuff]

Not sure what the purpose is, but you could always go:

=if(a1<10,0,left(a1,1,))

or

=floor(a1/10,1)

 

[pplstuff]

Also, if you only want to see the 0 - make a custom number format. Something like:

"0"0

or

"0"#

 

[xchoco]

here is what I am trying to do
Demand Code Row A1 to D1 = 0-1-0-1
Cell A2 I wrote the code 01
Cell B2 I wrote the prices is 100
Cell C2 I wrote the formula =IF(TEXT(LEFT(A6,1),"#")=TEXT(TOTAL!$F$1,"#"),IF(TEXT(RIGHT(A6,1),"#")=TEXT(TOTAL!$H$1,"#"),SUM(B6*60),SUM(B6*0.9)),SUM(B6*0.9))
but the answer is not correct it should be 6000 but in the formula only count it as 90
what wrong with the formula.
thankss

 

[pplstuff]

Well, you should change "sum(b6*60)" et al. to "b6*60" - you don't need the sum. The best suggestion I can give is to go in the formula bar, highlight different areas of the formula and hit "F9" key. This will show you values its calculating along the way and can help you find errors.

 

[xchoco]

Well, you should change "sum(b6*60)" et al. to "b6*60" - you don't need the sum. The best suggestion I can give is to go in the formula bar, highlight different areas of the formula and hit "F9" key. This will show you values its calculating along the way and can help you find errors.

can you tell me why the formula cannot read the 0 in cell A2 so I can make the answer of the formula correct.
thanks

 

[pplstuff]

T. Valko said it best - when data is stored as a number, it drops leading zeros. If the data is stored as text, it will see the zero (and show it to you in the cell) but you won't be able to perform any mathematical functions with it. Did you see the if() formula I posted earlier? Will that (or something like it) work as a workaround?

Example (using the last formula you posted):
=if(and(if(a2<10,0,left(a2,1,))=Total!$f$1,right(a2,1)=Total!$h$1),b2*60,b2*.9)

This has been adjusted a little to use and(). This should be close, hopefully you can adapt it to work.

If this isn't what you need, perhaps post more sample data: What is Total!f1 and h1? What is the purpose of a1:d1? etc.

 

[xchoco]

sorry the formula I give is wrong cell
Demand Code Row A1 to D1 = 0-1-0-1
Cell A2 I wrote the code 01
Cell B2 I wrote the prices is 100
Cell C2 I wrote the formula =IF(TEXT(LEFT(A6,1),"#")=TEXT(C1,"#"),IF(TEXT(RIGHT(A6,1),"#")=TEXT(D1,"#"),SUM(B6*60),SUM(B6*0.9)),SUM(B6*0.9))
plis help me repair this formula...
thankss

 

[pplstuff]

=if(and(if(a2<10,0,left(a2,1,))=c1,right(a2,1)=d1),b2*60,b2*.9)