extract date if count formula
 

Hello in cell Q2 I have the following information
EXT 1/5/08 RIVERS in cell "AT" I have
=IF(COUNT(FIND("/",Q2)),--MID(Q2,FIND("/",Q2)-3,8),"") but I get #VALUE!

what I want to do is extract the date, thanks for your help

extract date if count formula
 

Hi

Try this. It should format as a date but if not then set the date format you
need.

=RIGHT(LOOKUP(10^23,--LEFT(MID(Q2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Q2&"012 3456789")),99),ROW($1:$99))),6)+0

Mike

"Wanna Learn" wrote:

Hello in cell Q2 I have the following information
EXT 1/5/08 RIVERS in cell "AT" I have
=IF(COUNT(FIND("/",Q2)),--MID(Q2,FIND("/",Q2)-3,8),"") but I get #VALUE!

what I want to do is extract the date, thanks for your help

extract date if count formula
 

With
Q2: EXT 1/5/08 RIVERS

This might work for you...(formatted as a date)
=IF(COUNTIF(Q2,"*/*"),--TRIM(MID(Q2,FIND("/",Q2)-2,8)),"")

In the above example, the formula returns: 01/05/08

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Wanna Learn" wrote in message
...
Hello in cell Q2 I have the following information
EXT 1/5/08 RIVERS in cell "AT" I have
=IF(COUNT(FIND("/",Q2)),--MID(Q2,FIND("/",Q2)-3,8),"") but I get
#VALUE!

what I want to do is extract the date, thanks for your help

extract date if count formula
 

The problem is you have a blank space AND a character that looks like a
blank, but isn't (it has ASCII code 160... you probably got your text from a
website I'm guessing). Anyway, this formula should work for you...

=IF(COUNT(FIND("/",Q2)),--TRIM(SUBSTITUTE(MID(Q2,FIND("/",Q2)-3,8),CHAR(160),"")),"")

--
Rick (MVP - Excel)


"Wanna Learn" wrote in message
...
Hello in cell Q2 I have the following information
EXT 1/5/08 RIVERS in cell "AT" I have
=IF(COUNT(FIND("/",Q2)),--MID(Q2,FIND("/",Q2)-3,8),"") but I get
#VALUE!

what I want to do is extract the date, thanks for your help