CODEWITHSUNDEEP
excelvba
Abdul K
Abdul K

Reputation: 73

SUM a column which contains text and number

I have dataset in the format "Ping 172.123.123.123=[150ms]". How can i get the sum of what is within "[" and "]"?. I have many rows and columns and was hoping to get the SUM or AVERAGE of all ping

Example in the screen shotenter image description here

Upvotes: 0

Views: 40

Answers (2)

Xabier
Xabier

Reputation: 7735

This will sum each column and add the total to the last row of each column: 

   Sub foo()
    Dim openPos As Integer
    Dim closePos As Integer
    Dim midBit As String
    Dim Str As String
    Dim Extract_value As Integer
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    LastCol = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
    x = 1
    For x = 1 To LastCol
        For i = 1 To LastRow
            Str = Sheet1.Cells(i, x).Value
            On Error Resume Next
            openPos = InStr(Str, "[")
            On Error Resume Next
            closePos = InStr(Str, "m")
            On Error Resume Next
            midBit = Mid(Str, openPos + 1, closePos - openPos - 1)
            If openPos <> 0 And Len(midBit) > 0 Then
                Extract_value = Extract_value + midBit
            End If

        Sheet1.Cells(LastRow + 1, x).Value = Extract_value
        Next i
    Next x
    End Sub

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

Assuming that each cell ends with ]

 Public Function SumPings(CellsToSum As Range)
 Dim runtot As Double
 Dim r As Range
 Dim x As Integer
 Dim y As Integer
 Dim s As String
 For Each r In CellsToSum
     x = InStr(r.Text, "[")
     If x > 0 Then
         s = Mid(r.Text, x + 1, Len(r.Text) - x - 1)
         runtot = runtot + Val(s)

     End If
 Next r
 SumPings = runtot
 End Function

Upvotes: 1

Related Questions