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?
This will loop through Column A and place the sums in column B
I believe it will only work for single digitsCode
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
Your question reminds me of a trick designed by Bob Umlas ...:wink:
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 formula
2. Then in whatever cell ... just type in
Hope this will help
Gave me the idea to replace the spaces with "+"
Glad this could help you out ...:wink:
Evaluate can be a very handy function ...!!!