I have to apply several calculations to the number portion of a range of cell contents.
I trying to learn how to better describe a layout like this. 69(SP), 109(AN), etc. It's always this format with 1-3 digits then 2 letters in parenthesis.
Someone helped me at least removed the '(SP)' part in a whole different column so I could easily SUM, MAX, AVE, etc. for any given range, but then I lose track of the code. It's important too.
=MID(R3,FIND("(",R3)+1,FIND(")",R3)-FIND("(",R3)-1)
E.g. I need to SUM or get the AVE all the (SP)s, (AN)s, (AP)s and (AF)s in the range of A3-D3, R3-AA3 and down. Some sample values are 69(SP), 109(AN), 5(AP) and 98(AF).
I have the code so far to do MAX, and 2nd largest value in a range, but it's written in Greek too me. I've done some vba, but formulas just don't make sense to me! Here's the code: http://www.ozgrid.com/forum/showthread.php?t=203700
Thanks!
Mark