Find text separated by line breaks on a cell-by-cell basis

[TonyC]

Hi - a newbie here.

Is it possible in Excel to find text separated by line breaks within a cell? Suppose the text consists of the following in the same cell:

“aaa” on one line; a blank line; and then on the next line “bbb”.

How can I find all instances of this combination in a Worksheet containing thousands of rows?

Thanks in advance.

TC

 

[Logit]

Untested code :

Sub FindLineBreak()
Cells.Select
Selection.Find(What:=Chr(10), After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

Or you can:

1. Press Ctrl+F to display the Find tab of the Find and Replace dialog box. (See Figure 1.)
   
   
   
   
   Figure 1. The Find tab of the Find and Replace dialog box.
   
2. In the Find What box, hold down the Alt key as you type 0010 on the numeric keypad. It may not look like anything is in the Find What box, but the character is there.
3. Click Find Next.

 

[TonyC]

Thanks Logit. Looks to me like your suggestion will find line breaks but I'm looking for instances of the specific entry: "aaa"; blank line; "bbb" where aaa and bbb are variable from one search to another.

So for instance, one search might involve looking for cells containing:
2B>
(blank line)
3B<

where the actual text isn't going to be the same from one search to the next but the text I'm looking for will always be separated by a blank line.

Incidentally the 2 separated text items will always each consist of a block of 3 characters located at the beginning of a line within each cell.

TC

 

[vletm]

@TonyC
As many times...
Upload a Sample File to get a quicker response
then no need to guess ... guess ...

 

[TonyC]

@vletman - sorry I thought my issue was pretty straightforward but perhaps not so I'm uploading a file containing some typical content - see cells A1 and A2. Imagine that the worksheet actually contains thousands of cells in column A and suppose, for the sake of example, I want to find those cells (if any) containing the following:

3G>

1B<

Notice the two lines are separated by a blank line. All I need to know is which cell or cells contain this particular combination - I don't need to know where in the cell the combination is located as, once a cell has been identified, I can simply go to that cell and inspect it to check if it satisfies other criteria I'm interested in.

The above combination is just an example. On other occasions, it will be a different combination. In case it helps, I'll just mention that the first character of each block of 3 will always be selected from "1,2,3 and 4", the second will be "G or B" and the third will be selected from "> < ^ and V".

Cheers

TonyC

 

[Marc L]

Hi !

Formula in B1 :

=IF(ISNUMBER(SEARCH("3G>",A1))*ISNUMBER(SEARCH("1B<",A1))>0,"OK","")
​

 

[vletm]

@TonyC
If text would like to read 'only' normal direction (not backward)
then check my file too
You can get that text too.
There are few samples
..
and if really thousands of cells
... then maybe better do another way?

 

[TonyC]

Thanks vletm and MarcL for your efforts but it's not what I'm looking for. I don't really want to know if certain strings are both present somewhere in the same cell, I want to know if those strings are juxtaposed in the sense of being separated from each other by a blank line. Going back to my earlier example, a hit would be achieved if the VBA or formula located cells where the data is precisely related in this fashion:

2B>
(blank line)
3B<

In other words, the 2 is directly above the 3 (with an intervening blank line), the B is directly above the second B, and the > is directly above the <.

Of course, the characters in each string will differ from one search to the next. For instance, having searched on the above combination, the next search might be:

4G<
(blank line)
1B>

I suspect it's not trivial to solve but thanks again for your efforts to date which probably don't hit the nail on the head because my initial explanations weren't clear enough. Certainly it's way beyond me as a newbie!

TonyC

@TonyC
If text would like to read 'only' normal direction (not backward)
then check my file too
You can get that text too.
There are few samples
..
and if really thousands of cells
... then maybe better do another way?

 

[vletm]

Could You tell WHICH and WHAT are You waiting for?
Tell someway which rows ... whatever and
how You would like to get 'an answer'.
Next is the top rows of Your A1-cell.

If there is none of 'Your wanted answer' then
find / show some examples and
how did You mark those 'points'.

 

[Marc L]

TonyC,

I posted my formula 'cause here it is not the VBA section
but above all nothing matches between
your sample data and what you wrote !

What these data suppose to reflect ?
​

 

[TonyC]

@MarcL, @vletm

Thanks for persisting with this! I really appreciate your patience.

The example I gave above was to find the following:

3G>

1B<

in the Sample file.

If you go to the A2 cell, you'll find the example search query immediately following the line referenced 14 towards the bottom of the cell. Hope that clarifies what I'm trying to do.

The data by the way is to do with Challenge level square dance choreography. The character set "1B<" represents the number 1 man facing to the left. The search query above means I am looking for a formation in which #3 lady is "vertically" next to #1 man, with the lady facing right and the man facing left. Each cell in my worksheet represents a sequence of moves in which the dancers occupy various positions during the sequence. When searching, I'm looking for specific formations where the designated dancers, e.g. 3G and 1B, are together facing specific directions. When I find two cells where this applies, I can then generate a new sequence by combining data in one cell with data from the other cell.

Cheers

TonyC

 

[vletm]

Do You mean this 'place' which I marked with "3G>" "1B<" ?

If so, then I already gave Your wanted 'place' too with my file!

[B2:C2] from 531 character You take 9 characters
[G2] shows that range
[B3:C3] from 57 character You take 256 characters
[G3] shows that range

 

[TonyC]

@vletm

I attach the file I previously uploaded with the two sets of vertically aligned strings 3G> and 1B< highlighted in red and underlined (in A2 under item 14). I'm looking for some code which will find cells in which the two strings appear in that way - i.e. one above the other, vertically aligned with a blank line separating them.

Note, I'm not interested in the pair 3G> and 1B< highlighted in blue and underlined (again in A2 under item 12) because they're not vertically aligned.

Hope that makes more sense.

 

[vletm]

Now much better ...
but this would be tricky with just 'Ask an Excel Question'.
... but with VBA it would be possible
if those cells has 'made' just like Your sample file and so on ...
Anyway, it need crystal clear rules to 'pick' any those pairs
or how someone would like to get result?

 

[Marc L]

TonyC, maybe a formula champ' may resolve that but on my side
with such poor data design (Is Dumb or Dumber who made this ?‼)
easy way is a VBA code to just separate each data to individual cell
then after it will be easier to achieve whatever by formula or by code,
in case you accept to mod the layout …
​

 

[vletm]

@TonyC
'Data' is Okay ... for it's original use (dance choreography).
For other eyes ... it looks 'wow'!
But it's possible as I wrote.

 

[TonyC]

@vletm - exactly right. I've been using Excel for my choreo stuff for about 10 years and it's great for my particular needs even though the "data" may look funny to most people. Thanks for your interest and efforts (likewise to @Marc L). If you can suggest any other sites worth trying, I'd welcome your thoughts.

TonyC

 

[vletm]

@TonyC
I just did one 'sample' how to find ... something ... from that data
NOT READY at all.
Now You could
find 'criterias' Cells [D2] and [D3] (just those positions)
by select Cell [A1] or [A2].
You will get yellow cells if there are match with criterias.
As I asked ... how do You want to see results?
... and do You want to 'update' Your original data?

 

[TonyC]

@vletm

Thanks for getting back to me. You asked:

how do You want to see results?
... and do You want to 'update' Your original data?

Basically all I need to know is which "data" cells (all in column A) contain the data being searched. So for example if cell A500 returns a hit, then it would be great if B500 then gives the message "Yes".

I don't need any updating of the original data.

Cheers

TonyC

 

[vletm]

@TonyC
Ouch!
... now You'll get 'WOW'
I had already tested some ...
but I could almost change it as You wrote ... almost.
"data" is in A-column.
You can search with two 'parameters' (yellow boxes) => press [Do it]
It will show with yellow in A-column which data has matched.
You activate by mouse any of those A-column cell and
You will see with yellow-marks those matches.
There are also ... WOW ...
there are four colors with numbers and
eight 'codes' with 'symbols' ... okay?
Those 'symbols' can change ...
if MODE is bold then You'll get Your 'WOW' ... okay?
if You would like to see ONLY non-wow then press that [mode]-button until MODE will be 'normal'.
>> I didn't test all possible cases ...
You'll test and dance ...
>>> ideas ... ?

 

[TonyC]

@vletm Thanks again. I'll have a play with your file using my "data".

TonyC


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[jindon]

Not sure if this is how you wanted.

UDF
Use in cell like
=IsExists(A1,"3G>","1B<")

Function IsExists(txt As String, ByVal St As String, Ed As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .MultiLine = True
        .Pattern = "^(.*?)" & St & "[^\n]*\n{2}\1" & Ed & ".*"
        IsExists = .test(txt)
    End With
End Function

 

[TonyC]

@jindon Thanks for the suggestion but, in the meantime, I've discovered the CLEAN function (remember I'm a newbie) which enables me to search my data very effectively. First I copy my data cells to a separate worksheet, then render each cell "clean" using the CLEAN function, then do a "special paste" to a new column. That way I can search the new column (using CTRL-F) for the occurrences of the data relationships I'm looking for. For example, I can now easily find cells containing data representing a formation such as:

3G<
2B> 3B>
2GV 4G^
1B< 4B<
1G>

Thanks to everyone who came up with suggestions.