Results 1 to 1 of 1
Thread: Expanding formulae (Excel )
2007-01-25, 03:03 #1
- Join Date
- Feb 2001
- Yilgarn region of Toronto, Ontario
- Thanked 0 Times in 0 Posts
Expanding formulae (Excel )
Here is a method for building formula from component parts.
Some of you will know people like Karl, at work. Always helpful, turns on the machines AND CHECKS THEM before I arrive to deliver training, although his job is only to unlock the door.
So of course I'm happy to help him, when I can. Which means that now I get to help some of you, too, I hope. Good on yer, Karl!
Karl's problem is to parse a substring out of a string.
To me the Excel-only (no VBA please!) is judicious use of Instr, Right, left, Mid fucntions.
We've all done it, little functions here and there while we pluck up courage.
Then we laboriously copy and paste the component parts into a big ugly fucntion.
So I thought I'd have a shot at automating the copy-paste portion of my task.
Tha attached workbook has a SIMPLE version that scans a formula looking for a cell reference in the range of columns a-z, rows 1-9. Single letter, single digit ONLY!
It then fabricates the expanded string and this can be displayed in the immediate window, then pasted into an empty cell with a prefix of "=".
For example, with my cell pointer in cell G2, running the little macro TESTstrExpand produced the following line in the Immediate Window<pre>MID(A2,SEARCH(".",A2,1)+1,SEARCH(".",A2 ,SEARCH(".",A2,1)+1)-SEARCH(".",A2,1)-1)</pre>
This I have pasted into cell H2.
To me the restriction of A1 through Z9 is not serious, since I'm generally tooling around in that area anyway; Excel will take care of the real cell references as I copy/paste the expanded formula to its ultimate destination.
The cells outlined in pink represent my initial foray into string extraction.
The cells outlined in yellow represent my normal "AhHa!" result.
The cells outlined in green represent what I used to generate by judicious copy/paste, but now generate by the strExpand function.