extract date if count formula

  • Thread starter Thread starter Wanna Learn
  • Start date Start date
W

Wanna Learn

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
 
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&"0123456789")),99),ROW($1:$99))),6)+0

Mike
 
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)
 
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),"")),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula IF 1
SUMPRODUCT again! 5
SUMPRODUCT 3
Sumproduct formula 3
Complex Pivot Table Function 1
Sum of two groups 1
subtracting dates in an IF statement 2
Calculate Average Accross Multiple Worksheets 1

Back
Top