Search text in a cell - many words, may or may not be contiguous

[cacos]

Hi everyone!

I'm trying to check if a combination of 2 or more words are cotained in a given cell. The only problem is that I only get it to work if the words I'm looking for are one after the other- If they are in a different order, or have words in between, I'll get a FALSE.

Hope it's clear, this is my formula:

=ISNUMBER(SEARCH(A$1,$C2))

Let's say that A1 has the words "good movie blue" and that C2 has the words "not a good movie, it's blue"

I'm trying to get a TRUE in that scenario.

Thanks!!

 

[azumi]

You mean comparing partial lookups betwen 2 text? I have small project formula to find match First Name and Last Name. Please check the files

Azumi

 

[NARAYANK991]

Hi Lucas ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),1+99*(ROW(OFFSET($A$1,,,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),99)),C1)))

The text ( combination of words separated by a space character ) to be looked for : A1

The string of text to be looked in : C1

In your example :

A1 : good movie blue

C1 : not a good movie, it's blue

Copied from : http://excelhero.com/blog/

Narayan

 

[cacos]

It's perfect Narayan!

Though I'm getting an error when trying to enter it through VBA (needed in this case):

Range1.FormulaArray = "=AND(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",99)),1+99*(ROW(OFFSET($A$1,,,1+LEN($A$1)-LEN(SUBSTITUTE($A$1," ",""))))-1),99)),C1)))"

It throws a syntax error ("Expected end of statement") in this bit: " ",REPT(" "


Thanks

 

[cacos]

OK I got it to work by duplicating the "", so were it read " ", now it reads "" "".

But new problem: it doesn't move the last cell reference down (C1). It copies the formula down, but it keeps C1 as if it were locked ($C$1).

 

[NARAYANK991]

Hi Lucas ,

This is a new requirement that you want this in VBA ; let me check and I'll get back to you.

What is Range1 referring to ? Is it a range of cells ?

Narayan

 

[cacos]

Yes, Range1 refers to .Range("A2:A" & .Range("B65536").End(xlUp).Row)

 

[NARAYANK991]

Hi Lucas ,

The way the formula is drafted , it cannot be copied over a range of cells ; please understand that an array formula can be of two types ; one where the formula is operating on arrays of data , and because of that it needs to be entered using CTRL SHIFT ENTER ; another where the formula can be entered after selecting a multiple-row , multiple-column range , a simple example of which would be a formula using MMULT.

A formula such as the one we are discussing for your question falls in the first category ; it is to be entered using CTRL SHIFT ENTER because we are splitting the text within one cell into its constituents and forming an array ; we use this array in comparing the text within A1 to the text within C1. Thus the formula is basically a single-cell formula.

If this formula were to be entered over a multi-cell range , then it would have to form arrays within arrays ! The comparison would be between the range A2:A26 ( say ) and C2:C26 , and within each array , there would be further arrays.

The formula would be a totally different one.

If you wish to use the existing formula in VBA , you can only use it in one cell ; for the next cell , you would have to copy this formula down. Thus , you are using FormulaArray because you need to enter it using CTRL SHIFT ENTER , but the Range1 would have to be a single cell , and not a multi-cell range.

Narayan

 

[cacos]

Somehow, it worked using Paste as formula.


After entering the array formula programatically in A2:

    Sheet1.Range("A2").Copy
    Sheet1.Range("A3:A" & .Range("B65536").End(xlUp).Row).PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False

 

[cacos]

Just read your message Narayan, It makes complete sense and I understand now why it worked with the Paste special method.

Thanks again for your support and patience explaining!