Lookup Text in Comments

[malik641]

Is there a way to find certain text inside comments of cells?
What I'm looking to do is to search through a column of cells and only
look for text inside the comments that are placed in the cells IF there
are comments in those cells to begin with. So:

1. Find cells with comments
2. Find certain string of text within that comment
3. Take the number in front of the text and use that number to add to
the next number that will be found in the next string found

Example:
Comment in C1: "*1* V. Term."
Comment in C3: "*3* V. Term."

Cell D1: (*1* + *3*) = *4*

 

[David McRitchie]

You would have to write a macro, you can perhaps get some
ideas from http://www.mvps.org/dmcritchie/excel/ccomment.htm

and perhaps Debra has something more specific like:
Replace Old Name in Comments
http://www.contextures.com/xlcomments03.html#OldName

If not familiar with macros and user defined functions see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I think a little planning would have gone a long way to
have kept things simple. It would seem to me you should have had
that information in the next column, which you could hide or unhide
as you saw fit for reporting...

In fact toward a simpler solution, I'd use a function to
extract the comment, then another function to extract the number
and then the SUM worksheet function to add up the column.
Adding up numbers that can't be seen leaves a very poor
audit trail.

Function to obtain cell comments from another cell (#mycomment)
http://www.mvps.org/dmcritchie/excel/ccomment.htm#mycomment

Extract the First Set of Digits (#DigitsFirstID) -- Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm(#DigitsFirstID

D1: =personal.xls!igitsFirstID(personal.xls!mycomment(C1))
D2: =personal.xls!igitsFirstID(personal.xls!mycomment(C1))
D3: =personal.xls!igitsFirstID(personal.xls!mycomment(C1))
D4: =SUM(D$1:OFFSET(D4,-1,0) instead of SUM(D$13)-

 

[malik641]

I agree, but this spreadsheet was already made before I started workin
at my job and they want me to fix it and it would be nice to have tha
info in the next column, but the spreadsheet has been used for 2-
years and I don't feel like adding THAT many columns in.
But thanks for all that info I'll definitely see what I can do with it

I think a little planning would have gone a long way to
have kept things simple. It would seem to me you should have had
that information in the next column, which you could hide or unhide
as you saw fit for reporting...

[/color