Extracting only date from cell containing text and date

[jbhenry12]

Hello,

I have numerous cells in column D that contain the text "anticipated by DATE" or "DATE - expected" Where there is DATE, there are actual dates.

Is there a formula that will pull out only the date in these cells? The dates appear in the beginning and the end of the text strings, so I cannot just use a formula that removes the first or last text in the cell since the text is not uniform throughout.

Any help is appreciated.

Thanks!

 

[jai9]

Hi,

Assuming that the date is in MM/DD/YYYY format,

=MID(A1,FIND("/",A1,1)-2,10)

If date is in MM-DD-YYYY format, replace / with -.

Jai

 

[shawnhet]

Is it just those two phrases exactly or can there be a variety of text mixed in with the date?

If the former, you should just be able to use Replace to replace "anticipated by " with nothing and " - expected" with nothing. If the latter, you will need to post an example of precisely the format you are looking at.

Cheers,

 

[jbhenry12]

Shawnhet

This may be more complicated than I thought. I have text strings as follows...

Anticipated Vacancy 01/05/2012
Anticipated Vacancy 01/26/13; Promotion - Fill- duty station
Anticipated Vacancy TBD
Anticipated Vacancy June 2014

Ideally I would like to be able to pull back ONLY the following from all of these cells.

01/05/2012
01/26/13
TBD
June 2014


Is this even possible?


Thanks!

 

[shawnhet]

Assuming every line starts with "Anticipated Vacancy " and we don't want anything after a semicolon. Try the following:

=MID(A1,21,IF(ISERROR(FIND(";",A1)),999,FIND(";",A1)-21))

Cheers,

 

[jbhenry12]

Ok, that worked. BUT, this column is a little bit of a hodge podge. There are other cells with text and dates (example below)

Current Grade: B+
BBA 04/16/2010 (w/ extra)

So, it is picking up stuff after the 21st character and returning that as well.

Is there a way to get this to work for only the cells that start with "Anticipated Vacancy" ?

 

[shawnhet]

Not sure exactly what you're asking - if you only want the date for Anticipated vacancies this should work

=IF(ISERROR(FIND("Anticipated Vacancy",A1)),"",IF(FIND("Anticipated Vacancy",A1)=1,MID(A1,21,IF(ISERROR(FIND(";",A1)),999,FIND(";",A1)-21)),""))

This will leave a blank cell if A1 does not start with "Anticipated Vacancy"

If, OTOH, if you want to pull out all the dates from all lines, you will need to find a pattern in your data somewhere. If there is no pattern to the dates and no other pattern to the data there is probably not much you can do to get everything.

Cheers,