Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Cell Contents (Excel 2000)

    From another department, I receive a spreadsheet on a monthly basis that is a record of deposits. Often, if there was more than one deposit aggregated on a certain day, the entry in the cell, which say shows 300, will look like =50+150+75+25. Is there some code or a function that would extract each of those numbers out of the formula to say 4 separate cells in a column? I know that you can hit Ctrl+~ and see the formula but it would be useful for me to pull these cell contents.
    Steve

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract Cell Contents (Excel 2000)

    The following function will give the formula of a cell:

    eg: if Cell A1 has =50+150+75+25
    This formula will display the formula in A1 as text:
    =CellFormula(A1)


    Steve
    <pre>Option Explicit
    Function CellFormula(rng As Range)
    CellFormula = rng.Formula
    End Function
    </pre>


  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extract Cell Contents (Excel 2000)

    One way to do this is to format the data as Text, and then run Data, Text-to-Columns, using "+" as the delimiter. You will need to be certain you don't overwrite data in columns to the right of the source column. Also, you will end up with something like this if you overwrite the first cell:

    <table border=1><td valign=bottom>=1+2+3+4</td></table>becomes
    <table border=1><td align=right valign=bottom>=1</td><td align=right valign=bottom>2</td><td align=right valign=bottom>3</td><td align=right valign=bottom>4</td></table>
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extract Cell Contents (Excel 2000)

    John,

    You can do the Text-To-Columns without actually changing the format to Text.

    However this approach might run into difficulties if there is more than one math operator used in the formula.

    Andrew

Posting Permissions

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