Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    20
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Excel 2010 - extract date from date and text field

    Hi,

    I have a huge, multi tabbed excel ss. One (1) column has text and a date (IND 000 Change in Sponsor - January 26, 2006). This field is not consistant and will vary in text length and not all field will have a date. I just want to extract the date into a new cell, if it has one. Its not important what displays if there is no date.

    Any suggestions?

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is there any consistency at all? For example, when there IS a date, does it always have a " - " before it? Is it always the last portion of the text after the " - " ?

    If so, =IFERROR(RIGHT(A1,LEN(A1)-(2+FIND(" - ",A1))),"") might do the trick.
    Last edited by kweaver; 2013-12-10 at 12:13.

  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
    The modification to kweavers' formula to include the DATEVALUE function:
    =IFERROR(DATEVALUE(RIGHT(A1,LEN(A1)-(2+FIND(" - ",A1)))),"")

    will convert that text-string (that looks like a date) to an actual date that can be formatted and used in calculations.

    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
  •