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]

Hi

When you generate line breaks in this way, Excel inserts a hidden character at the line break.

When I tried it myself, it was character code 10.

So you can find the position of the line breaks with a formula like this

=FIND(CHAR(10),A1,1)

where A1 contains the text string.

Obviously this one only finds the first line break - if you have several line breaks per cell, you'll need something more elaborate.

 

[mancubus]

below code by Kenneth Hobs may help you.

[LEFT][COLOR=blue]Sub[/COLOR] Test_SplitToRightByVBLF() 

[COLOR=blue]'http://www.vbaexpress.com/forum/showthread.php?t=36416[/COLOR][/LEFT]
    [COLOR=blue]Dim[/COLOR] cell [COLOR=blue]As[/COLOR] Range 
    [COLOR=blue]For Each[/COLOR] cell [COLOR=blue]In[/COLOR] Range("A2", Range("A" & Rows.Count).End(xlUp)) 
        [COLOR=blue]If[/COLOR] cell.Value2 <> Empty [COLOR=blue]Then[/COLOR] SplitToRightByVBLF cell 
    [COLOR=blue]Next[/COLOR] cell 
[COLOR=blue]End Sub[/COLOR] 
 
[COLOR=blue]Sub[/COLOR] SplitToRightByVBLF(aRange [COLOR=blue]As[/COLOR] Range) 
    [COLOR=blue]Dim[/COLOR] a() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
    [COLOR=blue]If[/COLOR] aRange.Count <> 1 [COLOR=blue]Then[/COLOR] Exit [COLOR=blue]Sub[/COLOR] 
    a() = Split(aRange.Value2, vbLf) 
    aRange.Offset(0, 1).Resize(1, [COLOR=blue]UBound[/COLOR](a) + 1).Value2 = a() 
[COLOR=blue]End Sub[/COLOR]

 

[MickeyPvX]

Gerald, great advice! I had actually tried this not expecting it to work:

SEARCH("
",CX24,14)

But sure enough, it found that line break. Weird. As for that code, I'm afraid I'm not all that advanced, haven't touched the stuff since high school. Great name though, you a Doom fan?

 

[Gerald Higgins]

you a Doom fan?

not me

Glad it helped, do you still need help on this one ?

 

[MickeyPvX]

Glad it helped, do you still need help on this one ?

Not on this in particular, although how would you go skipping over spaces or line breaks to find, say, the third instance of whatever character I'm looking for?

 

[Gerald Higgins]

Let's say you want to find the third letter "a" in a text string in A1.

=FIND("a",A1,FIND("a",A1,FIND("a",A1,1)+1)+1)

Basically this works by finding the first example of "a", then starting from that point to find the second one, then starting from that point to find the third one.

Remember, FIND is case sensitive, SEARCH isn't.

Also, the formula quoted will return an error if there are less than 3 occurences of "a" in the string.
If this is a problem for you, there are various ways of handling that kind of error.

 

[MickeyPvX]

Good stuff...now for the big one. At the risk of going off topic, I'd like to transfer the theory of the nested =find() formula you've got there, and apply it to locating a particular cell in a row.

Basically, I've been taking an excel sheet generated by another software and pasting it into my workbook's first sheet so that the data from what I pasted can be extracted and used on the other many sheets in the workbook. However, the data I'm trying to make use of at this point in my project is proving hard for me to find correctly. Here's what I'm working with:

-I don't know the row or column of the cell I want, but the row title is always going to be, "Orbit", and it's always in column A.
-The cells in the row to the right of "Orbit" are all blank until you get to the first cell with data. I'm trying to find the second instance, which comes after a few more blank cells.
-I can find the first cell with data in that row with this formula:

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

I know I need to start searching the next cell over from that one, but can't quite figure it out.

 

[Gerald Higgins]

I think there must be something missing from that formula. Can you check it please ?

 

[MickeyPvX]

Doesn't look like it, I pasted that right back onto the sheet and it returned the correct data. I'm on Excel 2007 if that makes any difference.