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

 

[snoopyhr]

ok , lets try again:

in R2

=IF(LEN(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))))>0,LEFT(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))),FIND(" ",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))))-1),"")

Basically where there is A1 used replaced with
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))) <---- all these returns the content of every 4th row.

 

[brooksc29]

Haseeb, if you're still available, how would I combine

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

with

=IF(OFFSET(A4,2,1,1,1)="American League","American League",OFFSET(A4,3,1,1,1))

so, basically the reference to start the offset (A4) will fill the column down A8, A12, A16, A20, etc in each cell.

 

[Haseeb Avarakkan]

how would I combine

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

with

=IF(OFFSET(A4,2,1,1,1)="American League","American League",OFFSET(A4,3,1,1,1))

so, basically the reference to start the offset (A4) will fill the column down A8, A12, A16, A20, etc in each cell.

If i understood correctly, you mean;

If({B6,B10,B14....}="American league","American league",Extract date from {B7,B11,B15...........}). If so, try

R2, copy down....

=IF(INDEX($B$3:$B$1000,ROWS(R$2:R2)*4)="American League","American League",-LOOKUP(1,-LEFT(INDEX($B$4:$B$1000,ROWS(R$2:R2)*4),{1,2,3,4,5,6,7,8,9,10})))

 

[brooksc29]

Haseeb, thank you very much. That works like a charm.

would you mind taking a look at this problem if you have time?

http://www.mrexcel.com/forum/showthread.php?t=530581

thank you in advance

 

[brooksc29]

Haseeb,
you wrote:

If i understood correctly, you mean;

If({B6,B10,B14....}="American league","American league",Extract date from {B7,B11,B15...........}). If so, try

R2, copy down....

=IF(INDEX($B$3:$B$1000,ROWS(R$2:R2)*4)="American League","American League",-LOOKUP(1,-LEFT(INDEX($B$4:$B$1000,ROWS(R$2:R2)*4),{1,2,3,4,5,6,7,8,9,10})))

actually what I am trying to say is in cell $U2, look at cell $A5, find out if it says "American League" in cell $B8, then enter "American League", and if it doesn't say "American League" in $B8, then return the text in cell $B9.

this continues down the column, every 5 cells, so in $U3, it would need to look at cell $A10, find out if it says "American League" in cell $B13, then enter "American League", and if it doesn't say "American League" in $B13, then return the text in cell $B14.

this pattern needs to adjust 5 cells in col A and B for every one cell in col U, all the way down the column...

could you make that work?

 

[Haseeb Avarakkan]

Try;

U2, copy down...

=IF(INDEX($B:$B,5*ROWS(U$2:U2)+3)="American League","American League",INDEX($B:$B,5*ROWS(U$2:U2)+4))

 

[brooksc29]

very nice Haseeb, thank you.

and one more piece to the puzzle, how would I say =IF(INDEX($B:$B,5*ROWS(U$2:U2)+3)="American League", then OFFSET(INDEX($B:$B,5*ROWS(U$2:U2)+3)), 3, 2,1,1), OFFSET(INDEX($B:$B,5*ROWS(U$2:U2)+4),4,2,1,1)

i'm sorry, i had first put OFFSET (U2+3), but actually, what I am trying to do is pull the cell that is one to the right of the result of American League, into cell V2

 

[brooksc29]

I think I may have got it... I went with this...


=IF(INDEX($B:$B,5*ROWS(V$2:V2)+3)="American League",INDEX($C:$C,5*ROWS(V$2:V2)+3),INDEX($C:$C,5*ROWS(V$2:V2)+4))

 

[Haseeb Avarakkan]

Post deleted...