Display search terms in another cell

[k0st4din]

Hello
Could you help me with one of my dilemma they face.
I have a rather large file, but it is not so important, but only a single formula.
I use this formula:

=SUM(COUNTIF(M3;"*"&{"Monte negro";"Johnny";"Orange";"some"}&"*"))

Currently this is my formula if there matches search terms in cell M2, and shows me how many of them are in a text, but as a number.
Asking me how and what to change it to show me these words as words, not as a number.
I'll file example:
Once upon a time in Monte Negro boy namedJohnny,who do a lot of things, but mostly oranges, some of his wishes was to rest more.
What change in the formula that, for example in box L2, show me those words and not a 4? Or show me in L2 -> Monte Negro; Johnny; oranges; some


Thank you in advance for helping.

[k0st4din]

Can anybody help me?

[GTO]

Hi there,

I have seen worksheet functions combined in ways I certainly would not have imagined, but it seems less likely that you would get good results this way. For instance, your formula returns 4 for either "Once upon a time in Monte Negro boy namedJohnny,who do a lot of things, but mostly oranges, some of his wishes was to rest more." or "A boy named Johnny in Monte Negro often liked to eat some oranges. I wonder what kind of orange Johnny likes best?". So it seems to only catch the first instance of a sought after word. This hiccup would seem to me to be minor in comparison to returning the words found.

If you are okay with using VBA, maybe a UDF? Something like:

In a Standard Module:

Option Explicit
  
Public Function udfFindAllInstances(CellReference As Range, ParamArray LookFor() As Variant) As String
'             late-bound  | early-bound
Static REX    As Object   ' VBScript_RegExp_55.RegExp
Dim rexMC     As Object   ' VBScript_RegExp_55.MatchCollection
Dim rexM      As Object   ' VBScript_RegExp_55.Match
Dim SearchFor As String
Dim sRet      As String
Dim n         As Long
  
  If REX Is Nothing Then
    Set REX = CreateObject("VBScript.RegExp")
  End If
  
  SearchFor = vbNullString
  sRet = vbNullString
  
  If UBound(LookFor) > -1 Then
    
    For n = 0 To UBound(LookFor)
      SearchFor = SearchFor & "\b" & LookFor(n) & "\b|"
    Next
    
    SearchFor = Left$(SearchFor, Len(SearchFor) - 1)
    
    With REX
      .Global = True
      .IgnoreCase = True
      .Pattern = SearchFor
      
      If .test(CellReference.Text) Then
        
        Set rexMC = .Execute(CellReference.Text)
        
        For Each rexM In rexMC
          sRet = sRet & rexM.Value & ", "
        Next
        
        udfFindAllInstances = Left$(sRet, Len(sRet) - 2)
      Else
        udfFindAllInstances = vbNullString
      End If
    End With
  Else
    udfFindAllInstances = vbNullString
  End If
    
End Function

Due to switching computers, I do not have my old list of links at the moment, but here is one that Malcom gave me: http://msdn.microsoft.com/en-us/library/ms974570.aspx

You can see how the simple pattern is constructed, as well as how to make allowances for such cases as "orange" vs. "oranges" if you want both the singular and plural to return.

For instance, if you had "A boy named Johnny in Monte Negro often liked to eat some oranges. I wonder what kind of orange Johnny likes best?" in cell A4, then:
=udfFindAllInstances(A4,"monte negro","johnny","oranges","some") would return: Johnny, Monte Negro, some, oranges, Johnny
While:
=udfFindAllInstances(A4,"monte negro","johnny","orange(s?)","some") would return: Johnny, Monte Negro, some, oranges, orange, Johnny

Hope that helps,

Mark

[k0st4din]

Hello,
So I put the macro in a new module in the desired cell I put the formula to show me the search terms, but the attached picture you can look at that shows me an empty cell as if it did not contain those words.
Am I wrong somewhere or something?
Thank you for your cooperation.

[GTO]

Hello,
So I put the macro in a new module in the desired cell I put the formula to show me the search terms, but the attached picture you can look at that shows me an empty cell as if it did not contain those words...

Hi there,

I see that you are in London, what language is that? I don't "think" language should have any effect, just curious.

Did you read through the article at the link I provided? It is hard for me to tell in the picture, but it appears to me that there is an 'm' or a 't' abutted to the word sought in each case? If this is accurate, please read the article and you will see where the pattern would fall down. In short, you would want to ditch using the word boundary at each end of the word sought, as I presumed (likely incorrectly) that if we were looking for "Mary", we would not want to return "Mary, Mary" from "Mary and MaryAnn went to the creek.". Does that make sense?

Anyways, if taking out the word boundary symbol ("\b" without the quotes) fixes it, great! If not, please post an example workbook with enough strings to cover what all we might run into (and hope that brettdj shows up ).

Mark

[k0st4din]

Hello,
principle should not be a problem , but obviously it has to me , which makes me wonder .
Yes I live in England , but things I do in another language ( Bulgarian ) and this macro does not work in two directions :
If the word is not exactly written (you may have missed the whole point word) , two words can be merged due to not pressing the space bar did not report it as found. And second , what happened to me for the first time a macro is not to read any language other than English - which should not be produced .
Attachment in which the last two words in cell A1 are Bulgarian , but surely you can not read them , I do not know how much it would help. And two words (in the example because not care ) is not pressed interval , but the formula is correct cell C1 .
Thanks in advance .

[GTO]

Greetings,

I do not understand some of what you wrote, and would, if within my ability, like to help.

...principle should not be a problem , but obviously it has to me , which makes me wonder ....

Sorry, you lost me a bit; this may be non-critical.

...but things I do in another language ( Bulgarian ) and this macro does not work in two directions :
If the word is not exactly written (you may have missed the whole point word) , two words can be merged due to not pressing the space bar did not report it as found....

Not sure what you meant in the bolded part. I did cover my presumption as to not wanting to find words that have no discernible boundary (punctuation, a space, etc.). As I mentioned, try taking out the word boundary symbol in the loop that builds the pattern.

Upon doing so, I get:

Every, this, expensiveaccord, праскови, ортокали

Every, market

every, this

I would note that when supplying the arguments for the UDF, you must cover oddball stuff. For example, if you are looking for "expensive accord" and you want to handle the errant missed spacebar stroke, then this needs included in the argument, like "expensive ?accord". If you did not read the article at the link I provided, I would suggest doing so.

Also, in ditching the word boundary symbol, I see that we pick up the Bulgarian words. That troubles me, as there are clearly spaces (something that RegExp would or should pick up on) around the words.

For curiosity's sakes, is that "Peaches and Oranges"?

Mark

[k0st4din]

Hello,
First - do I have never criticized . On the contrary I am glad that there is someone who is trying to help.
What I wrote was that my colleagues who submitted the information , sometimes inadvertently leaked interval ( ie, human involuntary punctuation ) .
And asked me was associated with being located - >> and nearest to the desired word .
Ie if a formula rather than a macro was a trick that by placing a star before looking for something (* 'thing' *) , it found him excel .
Because I would not know where who will mistake anything , although as a rule to write the correct word .
And in your case if you put a question mark just a word , it means that I need to do to others, which I do not for me.
I looked at what you sent me and I read it , but a few samples I did not get back up.
Maybe I should look for another option?
For curiosity's sakes, is that "Peaches and Oranges"? ->> Yes, is it.

[Aussiebear]

Sorry but this latest post is very hard to follow. Mark has I believe attempted to answer your post but due to difficulties in the language gap, perhaps upset you somewhat. I do not believe that was his intention. So, in an attempt to get this back on a level playing field, would you kindly rewrite what the issue is, so that others can possibly assist.

[k0st4din]

Hello everyone.
I apologize if it is not understood what I need.
The idea is the following:
I have a column (A2: A15879 - for example) which have sentences of each row.
I somehow in column (B2: B15879) with a formula to be able to show me (out) my target words in these sentences.
The problem is that in some cases when certain words are typed, they merge accidentally.
I'll try to give you an example:
If cell A2 - the sentence - I like to eat peaches, especially with Johnny, my best friend.
Assuming that my search terms are: Johnny, best friend, peaches
it would have to check me, but this sentence sometimes inadvertently have normal human errors fusion of words and it could look different:
I like to eatpeaches, especially withJohnny, my bestfriend.
I like to eat peachesespecially with Johnnymy best friend.
I like to eat peache!, especially with Johnny, my best friend.
Even unintentionally wrong, I must again I can see these words - Johnny, best friend, peaches
Emphasising that this is just an example, my words are completely different than what I give an example.
I hope you can understand me.
Thanks in advance.

[Aussiebear]

Okay, with the example above you need to find partial strings (words) within Strings( accidentally merged words), but have you considered what you want done if a word is mis-spelt
I like to eatpeachs, especially withJohny, my bestfreind.
I like to eat peachesespecialy with Johnymy best freind.
I like to eat peache!, especialy with Johny, my bst friend.

The array of partial strings could become enormous to search for.

[k0st4din]

Okay, I do not think it would be much larger. Why did - because the first formula in the query I can ask and only the first letter of the word and this formula I found her, but my problem is that my shows numbers and I want to see the word. I turned the internet or maybe I did not ask the right search to find answers.
ie
Johnny loves to lunch and then eat peaches for dessert.
with this formula (there in my first post)
=SUM(COUNTIF(M3;"*"&{"Monte negro";"John";"Orange";"some";"peac"}&"*")) --->> 2
ctrl+shift+enter
I managed to find matches , which brings me to the thought that something must change in the formula itself, but I do not know how to assemble.

[GTO]

Hi there,

But as I pointed out, the formula (even for returning just a total) does not really work.

By example, take your formula as shown: =SUM(COUNTIF(M3,"*"&{"Monte negro","John","Orange","some","peac"}&"*"))
Place it in a cell in row 3 and copy down to row 8.

In M3:M8, enter:

Johnny loves to lunch and then eat peaches for dessert.
Johnny loves peaches for lunch and then eat peaches for dessert too!
Johnny loves something hearty for lunch and then eat peaches for dessert.
Somewhere around here is where Johnny raises peaches
I like peaches sometimes and I think we can buy some somewhere
Brian lives in Johnstown and describes himself as handsome and peaceful.

The returns are: 2;2;3;3;2;3

Notice that "peaches" is in the second one twice? So we should return 3, but we return 2. You can see the undesired results in the other lines, but let us just look at that last one. "Johnstown" is counted for "John", "Handsome" is counted for "some" and "peaceful" matches "peac*". Not exactly a trustworthy return.

As to fuzzy matches overall, you would likely need some rather advanced regular expression pattern(s) I imagine. A formula is not going to do it.

Mark

[Aussiebear]

Try this approach in the attached workbook. In Cells D13 I have selected random words from the strings in A1:A3, and in E1:E3 are the counts for these words. Change the individual words to reflect a new count. As you will see it will find even the "merged" words. Test this by changing the word in D1 to "expensive". Should you get to the stage of only wanting to find the exact word then remove the "*"&" from in front or behind the cell ID.

[k0st4din]

This will do for me.
Thank you very much for your cooperation on your part.