Search for "Enters"?

MickeyPvX

New Member
Joined
Feb 14, 2011
Messages
45
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
A slightly different approach.

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

Code:
=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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top