How to Add values starting with digits "0"

[friendlydue2000]

Hi Everyone,

Hope All is Well!!!

I have a small problem and need your assistance in this regard.

Problem Statement: I have numbers written in every single Cell in Excel. It may be in 2 cells if the value is 2 digits (eg 69), may be in 3 cells if the value is 3 digits ( for eg:101) etc. So at the end of particular rows we should add the total value and write down each digit in each cell. For eg ( Adding 69+101=170). This 170 should be written in 3 cells like "1" in 1st cell, "7" in 2nd Cell and "0" in 3rd Cell.

I Had used the Concatenate formula to bring down all the digits in seperate cell to single cell.So for eg: if its 129, it would come in concatenate formula as 00129 ( Because if the digits are in 1000's or more, then the zeroz are been replaced with digits).

I just require a simple formula which would remove the zeroz in the begginning and give me the added value of it.

Thanks

Awaiting Response

 

[alansidman]

If I am understanding you correctly, you have a cell with a number that has leading zeros. You would like to have the leading zeros removed.

If this is the case, assuming your numbers are in column A, then your formula in B1 would be =A1*1. Then copy this down.

Alan

 

[Oaktree]

You could use =TEXT(SUM(A1:A3),"00000") to get 00129

But, to break it in three cells, you could use
D1 =INT(SUM(A1:C1)/100) for the 100s digit
E1 =INT(SUM(A1:C1)/10)-D1*10 for the 10s digit
F1 =INT(SUM(A1:C1))-D1*100-E1*10 for the 1s digit

Or, if you have "00129" as the result, you can either use =(A1&B1&C1)*1 as Alan suggests above or =(A1&B1&C1)+0

Either *1 or +0 will convert your text string back to a number.

 

[friendlydue2000]

Thanks for giving the formula (=A1*1).

Suppose i have "00120952" in A1 Cell and in B1 Cell if i insert the formula "=A1*1, then i get "120952". The last two digits are always after point, in other words i need "1209.52" and not "120952".

Kindly assist in this regard


Awaiting Reply

Thanks

 

[cjcobra]

just divide by 100.

You could do this instead of the *1 to get what you need.

 

[friendlydue2000]

Oh no no!!! I can divide by 100 only if the number is "00120952. But if it is "01120952", i should divide by 1000. I cant keep changing the formula in every cell. The numbers may either be in 100's,1000's or 10,000's. But in all circumstances the one thing which stands common is the last 2 digits have to be before points.

For eg: if no is "00120952", then i want as "1209.52"
if no is "01120952", then i want as "11209.52"


Hope i am clear in explaining that.

Awaiting Reply

Thanks

 

[Oaktree]

01120952 / 100 = the 11209.52 you're looking for. Are you sure you can't just divide by 100 each time?