+ Reply to Thread
Results 1 to 6 of 6

Extract Latest Date from wall of text

  1. #1
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    166

    Extract Latest Date from wall of text

    Lets say i have a wall of text that looks like this, all of it in 1 cell

    Please Login or Register  to view this content.
    What formula do I use to extract the latest date from this string? In this example, the ideal result will be 02/05/2014

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Extract Latest Date from wall of text

    misunderstood the question...

    UPDATE:

    columns after B can be hidden. the formula in B1 will give you the latest date no mater how long your string, as long as you have your formula dragged as far right as possible...

    UPDATE:

    let's say your string is in A1, then put this formula in C1 and drag-fill right all the way up to the point that it starts spitting out blanks.

    Please Login or Register  to view this content.
    then, put this formula in B1:

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 03-30-2014 at 11:27 PM. Reason: updated with a proper solution
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    166

    Re: Extract Latest Date from wall of text

    Quote Originally Posted by icestationzbra View Post
    hi there,

    if the above value is in cell A1, use the following:

    Please Login or Register  to view this content.
    hmm code's not as robust as needed
    • the wall of text can be very very long
    • the desired output date can be anywhere in the string, it could be at the start one day and at the very end the next, it can be hidden behind an & or maybe not.

  4. #4
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    166

    Re: Extract Latest Date from wall of text

    Quote Originally Posted by icestationzbra View Post
    misunderstood the question...

    UPDATE:

    columns after B can be hidden. the formula in B1 will give you the latest date no mater how long your string, as long as you have your formula dragged as far right as possible...
    cant download the file. Maybe paste the code?

  5. #5
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    166

    Re: Extract Latest Date from wall of text

    Quote Originally Posted by icestationzbra View Post
    misunderstood the question...

    UPDATE:

    columns after B can be hidden. the formula in B1 will give you the latest date no mater how long your string, as long as you have your formula dragged as far right as possible...

    UPDATE:

    let's say your string is in A1, then put this formula in C1 and drag-fill right all the way up to Z1.

    Please Login or Register  to view this content.
    then, put this formula in B1:

    Please Login or Register  to view this content.
    [strike]tried it and all I got was errors
    ScreenHunter_02 Mar. 31 08.48.jpg[/strike]

    EDIT: Formula works but isn't robust enough, I snuck an extra few date in it and they weren't detected

    UPDATE: Answer obtained
    http://www.mrexcel.com/forum/excel-q...ml#post3764131
    Last edited by lordfa9; 03-31-2014 at 02:38 AM.

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extract Latest Date from wall of text

    Next time that you'll cross post pls add a link with your post in the other forum in your First post, because:

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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. how to extract latest date to a paticular date from diffrent date list
    By rudraalok in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2014, 05:18 AM
  2. [SOLVED] Need a function to return latest date after searching text in an adjacent column
    By kevindict in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2013, 12:50 PM
  3. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM
  4. Replies: 1
    Last Post: 01-20-2012, 09:44 AM
  5. extract the latest date given a certain parameter
    By mark896 in forum Excel General
    Replies: 8
    Last Post: 11-25-2010, 09:26 AM

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