Adding Numbers in the same Cell

EXCELn00b18 · Jan 12th 2018

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?

davesexcel · Jan 12th 2018

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

Display More

**Carim** · Jan 12th 2018

Hello,

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

Code

=EVALUATE(SUBSTITUTE(A2," ","+"))

2. Then in whatever cell ... just type in

Code

=Trythis

Hope this will help

davesexcel · Jan 13th 2018

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

Display More

**Carim** · Jan 13th 2018

Hi,

Glad this could help you out ...:wink:

Evaluate can be a very handy function ...!!!

Adding Numbers in the same Cell

Participate now!

Share