Search for "Enters"?

[MickeyPvX]

Quick question, is it possible to search for line breaks? For example, I've got a cell in which I want to search for a text string, but the text is separated by Alt+Enter's:

<TABLE style="WIDTH: 115pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=155 border=0><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" span=5 width=31><TBODY><TR style="HEIGHT: 35.25pt; mso-height-source: userset" height=47><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 115pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 35.25pt; BACKGROUND-COLOR: #99cc00" width=155 colSpan=5 height=47>(Time)
Name
Number
Duration
</TD></TR></TBODY></TABLE>

So when you put in a function to find the contents of that cell (like an index) it spits out a line of text with no spaces:

(Time)NameNumberDuration

So if I want to just pull out the name, number, or whatever, how can I do that?

 

[Gerald Higgins]

OK thanks I see what it's doing, although I don't fully understand it (I'm on 2003).

I'll look into that.

 

[MickeyPvX]

OK thanks I see what it's doing, although I don't fully understand it (I'm on 2003).

I'll look into that.

=HLOOKUP("*",INDEX(A1:BH33,MATCH("ORBIT",A1:A33,0),2):BH33,1,FALSE)

The way I built this was from inside out:

-First, the match("Orbit",a1:a33,0) finds the row number that the orbit information is in (let's say it's 10).
-That gets me index(A1:BH33,10,2), which is the next cell to the right of where it found "Orbit", so in this case cell B10. The :BH33 immediately following the index() function actually works to form a range, now B10 to BH33
-So now I've got =hlookup("*",B10:BH33,1,FALSE), and as I understand it, the "*" looks for any cell not blank in the top row of that range.

Now I just have to figure out how to skip over the first cell it finds, and return to me the second one.

 

[Gerald Higgins]

A slightly different approach.

I've changed the overall ranges.
This seems to work in a small test.

=HLOOKUP("*",OFFSET(B1,MATCH("ORBIT",A1:A20,0)-1,
MATCH(HLOOKUP("*",OFFSET(B1,MATCH("ORBIT",A1:A20,0)-1,0,1,20),1,FALSE),
OFFSET(B1,MATCH("ORBIT",A1:A20,0)-1,0,1,20),0)+1,1,20),1,FALSE)

I think this looks horrible so maybe there's a more elegant solution, but it does seem to work.

 

[MickeyPvX]

I've seen worse, but it works as advertised so far! I'm unfamiliar with the offset function, maybe I can mess around with it to see what I can get out of it in conjunction with the other ideas I've been toying with. Thanks!

 

[MickeyPvX]

Ok, so here's what I ended up with:

=HLOOKUP("*",OFFSET(B1,MATCH("orbit",A1:A33,0)-1,MATCH(HLOOKUP("*",INDEX(A1:BH33,MATCH("Orbit",A1:A33,0),2):BH33,1,FALSE),OFFSET(B1,MATCH("orbit",A1:A33,0)-1,0,1,40),0),1,59-MATCH(HLOOKUP("*",INDEX(A1:BH33,MATCH("Orbit",A1:A33,0),2):BH33,1,FALSE),OFFSET(B1,MATCH("orbit",A1:A33,0)-1,0,1,40),0)),1,FALSE)

Probably the most convoluted path to the desired result, but it works great, only searcing within the range I want, and starting where I want.