Add numbers in a single cell

[cdb5341mb]

Hello , I'm cdb5341mb, and I need help. I'm trying to add the numbers in a single cell and don't know if there is a formula for it. I tried everything I could think of but nothing works.

0 5 0 0	5
0 5 0 1	6
0 5 0 2	7
0 5 0 3	8
0 5 0 4	9
0 5 0 5	10
0 5 0 6	11
0 5 0 7	12
0 5 0 8	13
0 5 0 9	14
0 5 1 0	6
0 5 1 1	7
0 5 1 2	8
0 5 1 3	9
0 5 1 4	10

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

It seems to be easy but its not working and to do this in long hand is very time consuming .

If anyone can help I would be grateful.
Thank you in advance
cdb5341mb

 

[njimack]

Although this is possible with a formula (assuming each cell will always have 4 digits), a UDF would be more flexible:

Function Add_Cell(c As Range)

Dim i As Long


For i = 1 To Len(c)
    Add_Cell = Add_Cell + Mid(c, i, 1) + 0
Next i


End Function

Sheet1

*	A	B
1	0500	5
2	0501	6
3	0502	7
4	0503	8
5	0504	9
6	0505	10
7	0506	11
8	0507	12
9	0508	13
10	0509	14
11	0510	6
12	0511	7
13	0512	8
14	0513	9
15	0514	10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =Add_Cell(A1) B2 =Add_Cell(A2) B3 =Add_Cell(A3) B4 =Add_Cell(A4) B5 =Add_Cell(A5) B6 =Add_Cell(A6) B7 =Add_Cell(A7) B8 =Add_Cell(A8) B9 =Add_Cell(A9) B10 =Add_Cell(A10) B11 =Add_Cell(A11) B12 =Add_Cell(A12) B13 =Add_Cell(A13) B14 =Add_Cell(A14) B15 =Add_Cell(A15) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[cdb5341mb]

Thank you Neil, I will give it a try
cdb5341mb

 

[JoeMo]

The way you posted your "numbers" they appear to be digits with space delimiters. If that's true then here's an alternative UDF for you.

Excel Workbook
	A	B
1	0 5 0 0	5
2	0 5 0 1	6
3	0 5 0 2	7
4	0 5 0 3	8
5	0 5 0 4	9
6	0 5 0 5	10
7	0 5 0 6	11
8	0 5 0 7	12
9	0 5 0 8	13
10	0 5 0 9	14
11	0 5 1 0	6
12	0 5 1 1	7
13	0 5 1 2	8
14	0 5 1 3	9
15	0 5 1 4	10
Sheet2

Function AddCellNums(S As String)
For i = 0 To UBound(Split(S, " "))
    AddCellNums = AddCellNums + Val(Split(S, " ")(i))
Next i
End Function

 

[Mike LH]

Hi,

I'd do it with a formula.


=SUMPRODUCT(--MID(SUBSTITUTE(A1," ",""),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(A1," ","")),1)),1))

 

[Ron Coderre]

If there will always be 4 digits, separated by spaces...
try this:

=SUMPRODUCT(--MID(A1,{1,3,5,7},1))

If the length may vary, use this:

=SUMPRODUCT(--MID(A1,(ROW(INDIRECT("1:"&CEILING(LEN(A1)/2,1)))-1)*2+1,1))

Is that something you can work with?

 

[Rick Rothstein]

The way you posted your "numbers" they appear to be digits with space delimiters. If that's true then here's an alternative UDF for you.

Function AddCellNums(S As String)
For i = 0 To UBound(Split(S, " "))
    AddCellNums = AddCellNums + Val(Split(S, " ")(i))
Next i
End Function

Here is another way to write such a function, given your assumption (it's a one-liner)...

Function DigitSum(S As String) As Long
  DigitSum = Evaluate(Replace(S, " ", "+"))
End Function

 

[Rick Rothstein]

The way you posted your "numbers" they appear to be digits with space delimiters. If that's true then here's an alternative UDF for you.

Function AddCellNums(S As String)
For i = 0 To UBound(Split(S, " "))
    AddCellNums = AddCellNums + Val(Split(S, " ")(i))
Next i
End Function

Here is another way to write such a function, given your assumption (it's a one-liner)...

Function DigitSum(S As String) As Long
  DigitSum = Evaluate(Replace(S, " ", "+"))
End Function

And this function (another one-liner) will work whether the digits have spaces between them or not...

Function DigitSum(S As String) As Long
  DigitSum = Evaluate(Replace(StrConv(S, vbUnicode), Chr(0), "+") & "0")
End Function

 

[cdb5341mb]

Thanks to ALL of you that helped me solve this problem. IT'S WORKING.
The CODE and FORMULAS, all seem to do the job. Again thank you (niimack, JoeM, MikeLH, Ron Coderre and Rick Rothstein)
You guys are GREAT
Thank you cdb5341mb