lookup taking only part of result

[racer25]

Hi Guys

I have a problem, I am trying to do some sort of a lookup on a range o
cells and when Excel finds a match to take a mid string approach to th
data it finds.

Example 1

RM3c.Racer
This Player is on our complaint list.
Rank: 3-Star General ( 16 )

Assuming this data is in cells A1:A3 I want a formula to extract onl
the 3 Star General i.e. search for Rank then MID(Text,7,30) 7 in 3
characters long,

Example 2

RM3c.Racer
Rank: 3-Star General ( 16 )

The rank line is now on A2 here lies my problem, I have to do this fo
50 plus ppl.

Your help and guidance would be appreciated.

Ro

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64677

 

[Frank Kabel]

Hi
try
=TRIM(MID(A1,FIND("Rank:",A1)+6,50))

 

[racer25]

Maybe I did this wrong but this doesn't search a rabge of cells, Irank
could be in A2 or A3

Thanks for quick response,