+ Reply to Thread
Results 1 to 3 of 3

Extracting key financial data from cells

  1. #1
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Extracting key financial data from cells

    Hello Excel gurus,

    My question today relates to extracting some key information from cells. Unfortunately the text-to-columns tool in Excel does not work for me as I would like to see the extracted information in a specific format/layout. I've tried using it but the data does not get extracted very cleanly.

    My guess is that this will require left/mid/right, however the issue I'm running into is there are certain symbols like "$" or "(" in the raw data which are not unique in one text string so these formulas are not working for me. I've also tried using find/search but still running into a road block. I've attached a sample of the worksheet and outlined the raw data as it currently is and the extraction/result I would like to see. This raw data is in essence a financial report that I receive from the company system and the numbers are changing all the time.

    Any help would be appreciated as always.

    Thanking you all in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Extracting key financial data from cells

    Hello,

    as I see it, you need to use Find/Replace extensively before you can use the Text to Column tool.

    Replace $ with nothing
    Replace [space][space]A with [space]A
    Replace [space][space] with |
    Edit the first cell and insert a | symbol before the text.

    Run Text to Columns with the | as the delimiter and set it to treat consecutive delimiters as one.

    That will get you near the result. There is still some cleanup to do. Excel has no chance to recognize something like "$10,000,000.00)" as a negative value, because the leading bracket is missing.

    cheers, teylyn

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Extracting key financial data from cells

    slighlty different approach than teylyn

    using a couple of "cleasing formulas"

    extract row header
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then trimming formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    copy and paste the output and text to column delimit by space

    as teylyn mentioned...you wont be able to recognise asset5 row as negative number
    Attached Files Attached Files
    Last edited by humdingaling; 06-03-2015 at 12:36 AM. Reason: len doesnt need additional char as header taken care of extra spaces
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extracting Data from Coloured Cells
    By darrenfiend in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2015, 01:28 PM
  2. [SOLVED] Extracting data from Merged Cells
    By zloywolf in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2012, 02:09 PM
  3. Extracting Data in Multiple Cells
    By jdot in forum Excel General
    Replies: 7
    Last Post: 03-21-2012, 01:53 PM
  4. Replies: 1
    Last Post: 03-04-2012, 02:37 AM
  5. Extracting data into cells from string
    By secretg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2009, 09:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1