Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to Extract Data

    I have a spreadsheet where I need the following data extracted-see sample data extracted

    1) The first value after text for eg 10000- (see a1)
    2) The month & date after the first value (see A1 ) for eg 04/19
    3) The final value after the month and year (see A1) for eg 329,559.32

    Your assistance will be most appreciated
    Attached Files Attached Files
    Last edited by HowardC; 2011-05-04 at 22:39. Reason: Forgot Attachment

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    New Zealand
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HowardC View Post
    I have a spreadsheet where I need the following data extracted-see sample data extracted

    1) The first value after text for eg 10000- (see a1)
    2) The month & date after the first value (see A1 ) for eg 04/19
    3) The final value after the month and year (see A1) for eg 329,559.32

    Your assistance will be most appreciated
    The rows are fixed length so can be extracted via several methods.

    Excel has built in functionality for most of this if it is a one-off task. Use "Tools > Text to columns" and then follow the prompts (format your date as a date). On import, particular files also trigger similar functionality (.txt I think)

    If you're attempting something more complex (such as programming / automation then let us know)

  3. #3
    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
    How about the formulas:
    1) =TRIM(MID(A1,29,11))
    2) =TRIM(MID(A1,50,5))
    3) =TRIM(MID(A1,61,11))

    Steve

Posting Permissions

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