+ Reply to Thread
Results 1 to 14 of 14

Help with extracting dates out of brackets: complication...

  1. 10-17-2013, 12:25 AM #1
    leopoldc
    leopoldc is offline
    Registered User
    Join Date
    04-04-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Help with extracting dates out of brackets: complication...

    Hi Guys,

    I am not sure if this is possible at all - give it a shot anyways.

    The issue is that column O extracts the date out of column N, however at row we encounter a problem where there are 2 brackets information in a cell, therefore excel gets confused and throw back a #VALUE! - would I be able to let excel know I only want it to grab information from what's in the second brackets ie date date?

    Or any other way for excel to capture only the date data from the cell it reads from? It's been bugging me for a while now.... Since using all formula/function in column O and P I'd only to use formula/function without VB or macro.

    Is it possible?

    Thanks in advance for looking and attempting to solve this issue.; )
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-17-2013, 12:38 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Help with extracting dates out of brackets: complication...

    If the dates will always be in that format (2013-09-23...ie 4 digits-2 digits-2 digits), then try this...

    =DATEVALUE(LEFT(RIGHT(N2,11),10))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 10-17-2013, 12:42 AM #3
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Help with extracting dates out of brackets: complication...

    In O2 Cell

    =IFERROR(DATEVALUE(SUBSTITUTE(RIGHT(TRIM(N2),11),")","")),"")

    Drag it down…


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  4. 10-17-2013, 01:43 AM #4
    leopoldc
    leopoldc is offline
    Registered User
    Join Date
    04-04-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Help with extracting dates out of brackets: complication...

    thanks for the prompt reply guys- however one issue :

    #VALUE! has been fixed but now, if you've noticed that at row 20 and 28 where there are 2 builders: [Builder (date), Builder (date)] (separated by comma) it grabs the second date instead of the first one.

    I need it to do that #VALUE! thing and also, I need the first date when there's [Builder (date), Builder (date)]

    thanks guys
    Reply With QuoteRegister To Reply
  5. 10-17-2013, 01:49 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Help with extracting dates out of brackets: complication...

    that is a little different than your 1st request?

    would I be able to let excel know I only want it to grab information from what's in the second brackets ie date date
    Reply With QuoteRegister To Reply
  6. 10-17-2013, 01:56 AM #6
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Help with extracting dates out of brackets: complication...

    In O2 Cell

    =IFERROR(DATEVALUE(SUBSTITUTE(RIGHT(LEFT(N2,FIND(CHAR(10),N2)-2),11),")","")),IFERROR(DATEVALUE(SUBSTITUTE(RIGHT(TRIM(N2),11),")","")),""))

    Drag it down…
    Reply With QuoteRegister To Reply
  7. 10-17-2013, 02:12 AM #7
    Ghozi Alkatiri
    Ghozi Alkatiri is offline
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Help with extracting dates out of brackets: complication...

    try this one at O2

    =--LEFT(RIGHT(N2,11),10)

    copy down
    Reply With QuoteRegister To Reply
  8. 10-17-2013, 02:19 AM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Help with extracting dates out of brackets: complication...

    Ghozi, nice shortening of my suggestion from post # 2 However, it seems the OP has changed what they want...they now want the 1st date
    Reply With QuoteRegister To Reply
  9. 10-17-2013, 02:24 AM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Help with extracting dates out of brackets: complication...

    OK try this instead...
    =IF(ISNUMBER(--MID(N2,SEARCH("(",N2,1)+1,10)),--MID(N2,SEARCH("(",N2,1)+1,10),--LEFT(RIGHT(N2,11),10))

    (thanks for the assist, Ghozi )
    Reply With QuoteRegister To Reply
  10. 10-17-2013, 02:41 AM #10
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Help with extracting dates out of brackets: complication...

    @ FDibbins,

    Out of curiosity… What happens if the data is something like this…

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 10-17-2013, 02:57 AM #11
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Help with extracting dates out of brackets: complication...

    hi leopoldc, another alternative:
    =MID(N2,SEARCH("????-??-??",N2),10)+0

    or to be safer:
    =MID(N2,SEARCH("(????-??-??)",N2)+1,10)+0

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  12. 10-29-2013, 08:16 PM #12
    leopoldc
    leopoldc is offline
    Registered User
    Join Date
    04-04-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Help with extracting dates out of brackets: complication...

    Hi guys,

    I have an extra follow up question for you guys - If there were more than 1 date

    ie:

    PL & GF Holdings (hi) Pty Ltd (ba) def (2013-10-08),
    PL & GF Holdings Pty Ltd (2013-10-09)

    Would it be possible for Excel to pick the latest date?

    Thanks again guys
    Reply With QuoteRegister To Reply
  13. 10-29-2013, 08:37 PM #13
    leopoldc
    leopoldc is offline
    Registered User
    Join Date
    04-04-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Help with extracting dates out of brackets: complication...

    For example I have couple of new cases where:

    Patty Insurerbuild (WATER) (2013-01-02),
    Insurance Repairs Pty Ltd (2013-10-22)

    Patty Insurerbuild (WATER) (2013-12-02),
    Insurance Repairs Pty Ltd (2013-01-22)

    I am able to extract the 1st and 2nd dates; however can Excel extract the latest date automatically?

    As you can see the first group the latest date is 2013-10-22 (second line), second group its 2013-12-02 (first line)

    Thanks
    Last edited by leopoldc; 10-31-2013 at 10:57 PM.
    Reply With QuoteRegister To Reply
  14. 10-31-2013, 11:14 PM #14
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Help with extracting dates out of brackets: complication...

    try this:
    =MAX(--MID(N2,SEARCH("(????-??-??)",N2)+1,10),IFERROR(--MID(N2,SEARCH("(????-??-??)",N2,SEARCH("(????-??-??)",N2)+1)+1,10),0))

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting Text Between Brackets
    By McNulty in forum Excel General
    Replies: 6
    Last Post: 05-17-2016, 07:16 PM
  2. [SOLVED] Extracting multiple text within brackets
    By guapo in forum Excel General
    Replies: 10
    Last Post: 12-18-2012, 08:19 PM
  3. Extracting numerical data from brackets
    By gapink in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-10-2012, 11:15 AM
  4. Excel 2007 : Extracting text from within brackets
    By EMarky in forum Excel General
    Replies: 5
    Last Post: 08-09-2010, 03:50 PM
  5. Extracting a value from between brackets
    By James in forum Excel General
    Replies: 3
    Last Post: 02-04-2005, 11:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1