Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Extract numbers from a cell comment... (XL2003)

    Anyone know of a formula or bit of VBA that would extract numbers from a cells comments?

    Every day, I receive a spreadsheet from my corporate office. The information is nice, but I usually have to doctor it up a bit before I can really make use of it. I've attached a "watered-down" example of what I mean. In the attached sheet, a cell contains a percentage. That same cell also has a comment. The comment has the two numbers that were used to determine the percentage. The percentage actually does me little good. It's the underlying numbers or comment contents that I need.

    Hard to explain, but simple to see if can take a looksie...

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract numbers from a cell comment... (XL2003

    Ricky

    This code seems to work.

    Function ExtractNumber(rCell As Range)
    Dim iCount As Integer, i As Integer
    Dim sText As String
    Dim lNum As String



    sText = rCell

    For iCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, iCount, 1)) Then
    i = i + 1
    lNum = Mid(sText, iCount, 1) & lNum
    End If

    If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount


    ExtractNumber = CLng(lNum)
    End Function


    From our friends at Ozgrid
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract numbers from a cell comment... (XL2003)

    How about this function?

    <pre>Function ExtractComment(rCell As Range, iItem As Integer)
    Dim sCmt As String
    Dim iStart As Integer
    Dim iEnd As Integer

    sCmt = rCell.Cells(1).Comment.Text
    Select Case iItem
    Case 1
    iStart = InStr(sCmt, "TY: ")
    iEnd = InStr(sCmt, vbCrLf)
    If iStart * iEnd <> 0 Then
    iStart = iStart + 4
    iEnd = iEnd - iStart
    ExtractComment = Val(Mid(sCmt, iStart, iEnd))
    End If
    Case 2
    iStart = InStr(sCmt, "LY: ")
    iEnd = Len(sCmt)
    If iStart * iEnd <> 0 Then
    iStart = iStart + 4
    iEnd = iEnd - iStart + 1
    ExtractComment = Val(Mid(sCmt, iStart, iEnd))
    End If
    Case Else
    ExtractComment = CVErr(xlErrNum)
    End Select
    End Function</pre>


    Add it to a module then in a cell enter:
    <pre>=extractcomment(B7,1) to get the "TY"</pre>

    or
    <pre>=extractcomment(B7,2) to get the "LY"</pre>


    Steve

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank you

    Steve and Jezza Bear,

    Thanks for your help.
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help with UDF - Extracting Info from Comment

    The formula and the code noted below work fine, except for one small detail. In a cell, (Sheet1, A1), I have the following formula: =ExtractComment(Sun!C110,1).

    And the formula provides the needed number from the cell comment. But if I delete the entry and comment in cell (Sun!C110), the resulting number in (Sheet1, A1) doesn't update. The number stays. In other words, the formula in (Sheet1, A1) doesn't seem to notice if I delete or change the entry in (Sun!C110).

    Calculation is set to Automatic.

    Do I need to add something to the code (UDF) below, to force it to change or update?

    <font color=blue>Existing VBA:</font color=blue>
    <pre>Function ExtractComment(rCell As Range, iItem As Integer)
    Dim sCmt As String
    Dim iStart As Integer
    Dim iEnd As Integer

    sCmt = rCell.Cells(1).Comment.Text
    Select Case iItem
    Case 1
    iStart = InStr(sCmt, "TY: ")
    iEnd = InStr(sCmt, vbCrLf)
    If iStart * iEnd <> 0 Then
    iStart = iStart + 4
    iEnd = iEnd - iStart
    ExtractComment = Val(Mid(sCmt, iStart, iEnd))
    End If
    Case 2
    iStart = InStr(sCmt, "LY: ")
    iEnd = Len(sCmt)
    If iStart * iEnd <> 0 Then
    iStart = iStart + 4
    iEnd = iEnd - iStart + 1
    ExtractComment = Val(Mid(sCmt, iStart, iEnd))
    End If
    Case Else
    ExtractComment = CVErr(xlErrNum)
    End Select
    End Function</pre>


    Thanks,
    - Ricky

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with UDF - Extracting Info from Comment

    This is problematic: editing a comment doesn't trigger any event. Excel doesn't consider a change in a comment as a change of the cell or worksheet.
    If you insert the line

    Application.Volatile

    at the beginning of the function, it will be recalculated whenever the worksheet is recalculated, for example when you edit the value of any cell, or if you press F9. So changing the cell value of C110 (or any other cell) will cause the formula to be updated, but after entering, editing or deleting the comment you'll have to press F9 to update.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank you

    As you've sugggested, I've added the additional line to the front of the code.

    It was strange, tapping the F9 key and the formula results would not change even when the contents of the target cell had been deleted. About the only way I could get it to update was to delete the cell contents, close the workbook and then reopen. It's also an oddity that when you select a range and tap delete, the comments remain. Deleting the cell comments require an extra step!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Thank you

    I think it is "by design" that deleting cell contents does not delete the cell comment. There is no a priori reason why the comment should be deleted. It could contain an instruction for filling in a new value, for example. If you wish to delete contents and comment (and formatting) in one fell swoop, select Edit | Clear | All.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •