Extract numbers from comment and add them

Question

As the title suggests I am looking for a way to retrieve all the numbers from a cell comment and add them up. The only way I can think to do this would be to retrieve the comment as a string, assign each set of numbers to a variable, then add up the variables?

I am having a hard time with the logic, I don't know a way to retrieve the numbers out of a comment.

So far I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varComment As String
For i = 19 To 30
If Not Intersect(Target, Range("N19:N30")) Is Nothing Then
    On Error Resume Next
    varComment = Cells(Ni).Comment.Text
    Next i
End If
End Sub

The use is that I have a comment in cells N19:N30 that contains dollar values, "Food - $20, Gas - $40, etc..." I want the cell value to be updated anytime a new listing is made to reflect the total cost. Make sense?

Storax · Accepted Answer · 2018-08-12T06:57:51.867

Without making any assumptions on the numbers I would extract the numbers with a regex expression and then sum them up. I used a function found here and modified it slightly.

Function CleanString(strIn As String) As String
Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        '.Pattern = "[^\d]+"
        .Pattern = "[^0-9" & Application.DecimalSeparator & "]"
        CleanString = .Replace(strIn, vbCrLf)
    End With
End Function

With this function you can then add up the numbers in a comment

Function commentSum(cmt As Comment) As Double

Dim vDat As Variant
Dim i As Long
Dim res As Double

    vDat = Split(CleanString(cmt.Text), vbCrLf)
    For i = LBound(vDat) To UBound(vDat)
        If Len(vDat(i)) > 0 Then
            res = res + CDbl(vDat(i))
        End If
    Next i
    commentSum = res
End Function

For testing purposes

Sub TestCmtAdd()
Dim rg As Range
Dim sngCell As Range

Set rg = Range("A1:A10")

For Each sngCell In rg
    If Not (sngCell.Comment Is Nothing) Then
        MsgBox "Sum of numbers in comment of cell: " & sngCell.Address & " is " & commentSum(sngCell.Comment)
    End If
Next

End Sub

Everything seems to be working very well, is there a way I can have this macro initiated whenever a cell comment is changed? Currently it is set to a selection change but that has obvious drawbacks. — Kevin P., Aug 12 '18 at 21:09
That is probably worth a new question. I do not know a short answer how to monitor changes in a comment. Maybe it's enough to run the macro before you close or save the document. — Storax, Aug 13 '18 at 09:15

MsAmeen · Answer 2 · 2018-08-12T00:54:29.990

My below code is working under the following assumption:- -

Each Number MUST be start with "$" (spaces between $ and the Number will be trimed)
Each Number MUST end with "," (spaces between "," and the Number will be trimed)
Your "varComment" is already populated

Note: Split the comment with "vbCrLf" did not work with me

Dim SplitedComment() As String
Dim tmpStr As Variant
Dim DolarSignLoc, yourSum As Integer

' For Each Comment, Do the following
SplitedComment() = Split(varComment, ",")   ' Split the Comment by ",", we'll need ONLY the output that Contain "$" ( some of the output may NOT contain that char)

yourSum = 0     ' initialize your Sum Variable
For Each tmpStr In SplitedComment  ' for each Text in the SplittedComment
    DolarSignLoc = InStr(tmpStr, "$")   ' Get the Location of the "$" ( ZERO if not exist)
    If DolarSignLoc > 0 Then            ' ONLY Process the Text if contains "$"
        tmpStr = Right(tmpStr, Len(tmpStr) - DolarSignLoc)  ' Excetract your Number
        yourSum = yourSum + CInt(Trim(tmpStr))              ' Add to your Summation
    End If
Next

Extract numbers from comment and add them

2 Answers2