Announcement

Collapse
No announcement yet.

Adding Numbers in the same Cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Adding Numbers in the same Cell



    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?

  • #2
    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
    Dave
    XLorate

    Comment


    • #3
      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 formula
      Code:
      =EVALUATE(SUBSTITUTE(A2," ","+"))
      2. Then in whatever cell ... just type in
      Code:
      =Trythis
      Hope this will help
      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


      • #4
        @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
        Dave
        XLorate

        Comment


        • #5


          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

          Working...
          X