Pulling specific text from a cell

[brooksc29]

in Cell A1=
<table border="0" cellpadding="0" cellspacing="0" width="405"><col width="405"><tr height="20"> <td style="height: 15pt; width: 304pt;" width="405" height="20">4/4/2010 to 4/4/2010 League Batting Leaders (Totals) - Top 25</td> </tr></table>

I'd like to put a formula in cell R1 that just pulls the single date out of the text. So the result in R1 will be 4/4/2010.

How can I do that?

Thank you very much in advance

 

[VoG]

Try

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))

and format as Date.

 

[brooksc29]

i like it. thank you!

now, I have a question about applying the quick fill for the formula for the column.

The dates I need are every 4 cells, so cell A4, A8, A12, A16, A20, etc. How can I fill the column so that it stays in this order all the way down?

 

[snoopyhr]

You can modify Vog's formula as such:

=if(mod(row(a1),4)=0,DATEVALUE(LEFT(A1,FIND(" ",A1)-1)),"")

and copy down.

 

[brooksc29]

so if R2 - which needs to pull from A4
and R3, needs to pull form A8

this would be the formula in R2?
=IF(MOD(ROW(A4),4)=0,DATEVALUE(LEFT(A4,FIND(" ",A4)-1)),"")

when I pull that down, it still pulls A5, A6, A7, etc, when I need to go A4, A8, A12, A16, etc...

 

[brooksc29]

i'd like to post the worksheet, just not sure how

 

[snoopyhr]

Try this , placed in R2

=INDEX($A$1:$A$32,SMALL(IF(MOD(ROW($A$2:$A$32)-ROW($A$2)+1,4)=0,ROW($A$2:$A$32)-ROW($A$2)+1),ROWS($D$2:D2)))

Adjust the range as required or create a dynamic range.

And remember to confirm the formula with Ctrl Shift Return. this is array formula

 

[brooksc29]

OK, i think that will work, but I think I still need the LEFT( formula in there, or DATERANGE.

b/c in the cell is
<table border="0" cellpadding="0" cellspacing="0" width="405"><col width="405"><tr height="20"> <td style="height: 15pt; width: 304pt;" width="405" height="20">4/4/2010 to 4/4/2010 League Batting Leaders (Totals) - Top 25</td> </tr></table>

and I only need the date

 

[Haseeb Avarakkan]

Try;

B1 & copy down...

=LEFT(INDEX(A:A,ROWS(B$1:B1)*4),FIND(" ",INDEX(A:A,ROWS(B$1:B1)*4)))+0

Format the cell as date.

 

[brooksc29]

Thank you Haseeb!

That is super smooth!