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

#### xchoco

##### New Member
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 Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

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

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

or

=floor(a1/10,1)

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

"0"0

or

"0"#

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

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.

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

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.

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

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

Replies
6
Views
191
Replies
3
Views
123
Replies
2
Views
255
Replies
4
Views
163
Replies
3
Views
302

1,203,082
Messages
6,053,419
Members
444,662
Latest member
AaronPMH

### 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.

### Which adblocker are you using?

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

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