Results 1 to 1 of 1
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    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.
    Attached Files Attached Files

Posting Permissions

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