Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Text in Comments
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* -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=383904 |
#2
|
|||
|
|||
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....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$1:D3)- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "malik641" wrote in message ... 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* -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=383904 |
#3
|
|||
|
|||
I agree, but this spreadsheet was already made before I started working at my job and they want me to fix it and it would be nice to have that info in the next column, but the spreadsheet has been used for 2-3 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'm a novice at macros.David McRitchie Wrote: 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] -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=383904 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do you find and replace text in autoshapes or comments | Excel Discussion (Misc queries) | |||
Lookup returns #NA when search value (text) has leading zeros. | Excel Worksheet Functions | |||
Can Comments be automatically converted to text cell values? | Excel Discussion (Misc queries) | |||
how do i set default font / text size in comments? | Setting up and Configuration of Excel |