show Zero when separating numbers to individual cells

[jhere01]

here's my question... I have cell A1 with 10230, I want Cell B1 to show 1, C1 to show 0, D1 to show 2, E1 to show 3 and F1 to show 0.
for example i tried =value(Right(A1,1)) in cell F1 but got an answer of 3 not the 0 i was expecting.
please help....

 

[MarcelBeug]

In B1, copy to the right:

=--MID($A1,COLUMNS($B1:B1),1)

 

[gaz_chops]

Works for me.

[TABLE="width: 390"]
 <colgroup><col width="65" span="6" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 65, align: right"]10230[/TD]
  [TD="class: xl63, width: 65, align: right"] 1 [/TD]
  [TD="width: 65, align: right"]0[/TD]
  [TD="width: 65, align: right"]2[/TD]
  [TD="width: 65, align: right"]3[/TD]
  [TD="width: 65, align: right"]0[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]

 

[jhere01]

In B1, copy to the right:

=--MID($A1,COLUMNS($B1:B1),1)

works Great, except when theres a leading Zero

 

[Rick Rothstein]

works Great, except when theres a leading Zero

Is your cell formatted as text with the number having the leading zero typed into it... or is your cell formatted as General with the leading zeroes coming from custom cell formatting?

 

[jhere01]

its a custom format, will lead with zeros if the value is less than the amount of digits.

 

[jhere01]

the numbers are coming from a different spreadsheet, also came across this other error...
the numbers in the cells are visible as whole numbers but you click on the cell, for example cell shows 0067 but when you click on the cell the actual value is 67.2.
so when it gets to the third digit the cell gets a error #VALUE! in place of the decimal. I don't want the decimal just want each cell to show 1 digit.
hope that makes sense.

 

[MarcelBeug]

This will work if you have 5 digits (incl. leading zeroes) plus 3 decimals. In B1 and copy thru I1:

=RIGHT(INT(($A1*10^4)/(10^(COLUMNS(B1:$I1)))),1)

 

[Rick Rothstein]

the numbers are coming from a different spreadsheet, also came across this other error...
the numbers in the cells are visible as whole numbers but you click on the cell, for example cell shows 0067 but when you click on the cell the actual value is 67.2.
so when it gets to the third digit the cell gets a error #VALUE! in place of the decimal. I don't want the decimal just want each cell to show 1 digit.
hope that makes sense.

Does this modification to the formula MarcelBeug posted in Message #2 work for you then...

=MID(TEXT($A1,"0000"),COLUMNS($B1:B1),1)

Note that the digit output from this formula are text, not numbers. If you want the output to be real numbers, then I would write it this way...

=IFERROR(--MID(TEXT($A1,"0000"),COLUMNS($B1:B1),1),"")

 

[jhere01]

THANK YOU ALL for your help. it worked!!!