I have a Column of cells that I get from another program. In the column some cells are blank, some cells have 1 number, and then some cells have several numbers separated by 3 spaces EX.(1 1 5 4). Is there a way that I can add these values together in one cell so in my example i would add them in to one cell valuing 11?
Announcement
Collapse
No announcement yet.
Adding Numbers in the same Cell
Collapse
X

This will loop through Column A and place the sums in column B
I believe it will only work for single digits
Code:Sub getItNum() Dim LstRw As Long, Rng As Range, C As Range Dim s As String, n, y As Integer LstRw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & LstRw) For Each C In Rng.Cells s = C For n = 1 To Len(s) If IsNumeric(Mid(s, n, 1)) Then y = y + Mid(s, n, 1) End If Next n C.Offset(, 1) = y Next C End Sub

Hello,
Your question reminds me of a trick designed by Bob Umlas ...
Say your cell is A2 ... and it is holding : 1 1 5 4
1. Create a name ( Ctrl F3 ) e.g. Trythis and in Refers To ... place this formulaCode:=EVALUATE(SUBSTITUTE(A2," ","+"))
Code:=Trythis
If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...
 1 like
Comment

@Carim,
That great,
Gave me the idea to replace the spaces with "+"
Code:Sub getItNumB() Dim LstRw As Long, Rng As Range, C As Range LstRw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A2:A" & LstRw) For Each C In Rng.Cells C.Offset(, 1) = Evaluate(Replace(C, " ", "+")) Next C End Sub
 1 like
Comment

Hi,
Glad this could help you out ...
Evaluate can be a very handy function ...!!!If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...
Comment
Comment