1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'VBA Macros' started by TonyC, Jan 15, 2017.

  1. TonyC

    TonyC New Member

    Messages:
    10
    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
  2. Logit

    Logit Member

    Messages:
    67
    Untested code :
    Code (vb):

    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.)
      [​IMG]

      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.
  3. TonyC

    TonyC New Member

    Messages:
    10
    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
  4. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @TonyC
    As many times...
    Upload a Sample File to get a quicker response
    then no need to guess ... guess ...
  5. TonyC

    TonyC New Member

    Messages:
    10
    @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

    Attached Files:

  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,027
    Hi !

    Formula in B1 :

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

    vletm Well-Known Member

    Messages:
    2,544
    @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?

    Attached Files:

  8. TonyC

    TonyC New Member

    Messages:
    10
    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


  9. vletm

    vletm Well-Known Member

    Messages:
    2,544
    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.
    Screen Shot 2017-01-17 at 19.34.04.png
    If there is none of 'Your wanted answer' then
    find / show some examples and
    how did You mark those 'points'.
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,027
    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 ?
  11. TonyC

    TonyC New Member

    Messages:
    10
    @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
  12. vletm

    vletm Well-Known Member

    Messages:
    2,544
    Do You mean this 'place' which I marked with "3G>" "1B<" ?
    Screen Shot 2017-01-17 at 21.43.34.png
    If so, then I already gave Your wanted 'place' too with my file!
    Screen Shot 2017-01-17 at 21.50.46.png
    [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
  13. TonyC

    TonyC New Member

    Messages:
    10
    @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.

    Attached Files:

  14. vletm

    vletm Well-Known Member

    Messages:
    2,544
    Screen Shot 2017-01-17 at 23.04.30.png
    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?
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,027
    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 …
  16. vletm

    vletm Well-Known Member

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

    TonyC New Member

    Messages:
    10
    @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
  18. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @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?

    Attached Files:

  19. TonyC

    TonyC New Member

    Messages:
    10
    @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
  20. vletm

    vletm Well-Known Member

    Messages:
    2,544
    @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 ... ?

    Attached Files:

  21. TonyC

    TonyC New Member

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

    TonyC


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

  22. jindon

    jindon Well-Known Member

    Messages:
    503
    Not sure if this is how you wanted.

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

    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

    Attached Files:

    Marc L likes this.
  23. TonyC

    TonyC New Member

    Messages:
    10
    @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.

Share This Page