MID Formula help - when last digit value is 0

[Glaswegian]

Hi

Long time no see! Stuck with a formula again.

I have sheet where users input one set of numbers, a second set is determined by a calculation and the third set is the difference between set 1 and set 2. Each digit is in a cell, rather than the full value being in a cell. The actual calculation for set 3 is done 'out of sight' and the full value is in its own cell. I'm using a mixture of LEFT, RIGHT and MID formulae to determine the values for the third set and place each digit in its own cell. All works fine unless the final digit of set 3 is a zero. So a result of 99.78 is no problem - I can bring back the individual digits to their own cells. But a result of 99.80 results in problems - it shows as 99..8. The decimal point is ignored as I have a separate cell for that, so I can concentrate on the numbers. But when the last digit is 0, my formula returns the decimal point and seems to ignore the zero. The calculation start point is simply

=AH15-AH16

and the cell is formatted to 2 decimal places.

For the values after the decimal point I'm using

=IF(P17="","",RIGHT(AH17,1))

and then

=IF(O17="","",MID(AH17,LEN(AH17)-1,1))


Formulae for digits preceeding the decimal point work fine.

Hopefully I've explained this clearly.

I was never very good at formulae so any help would be greatly appreciated. Thanks.

[Paul_Hossler]

try this formula in the right places, maybe a helper column

=--TEXT(ROUND($A$1,2),"#0.00")

Or post a sample workbook

Paul

[snb]

1/100:

=RIGHT(TEXT(A22,".00"),1)

1/10

=RIGHT(TEXT(A22,".0"),1)

[Glaswegian]

Thanks to you both for your replies.

snb - that works a treat -thanks!