Substring Search within an Array of Strings (PART 2)

[voryzover]

Everyone, I posted the following below yesterday and got help from Yogi and Asala42.
When I posted this, it didn't occur to me that I would want to locate the main string in which the substring is found.

So to summarize: Search for a string (say rang) inside an array of strings placed over a range (say {"apple","orange","lemon"} placed at A1:A3). The command should return the 1st string that it finds a match (in that case this would be "orange")

My yesterday's entry is right below and I also pasted the link to the related discussion.

And as always, thanks a lot.

------------------------------------------

Hi everyone

First let me express my grattitude for everyone taking their time to answer questions on
this board. At least personnally speakig, this board is extremely valuable.

On to question: I know this must be a piece of cake for the experts.

I wish to search for a substring within an array of strings and just return back an indication
that the substring exists withing that array.

so for instance let the substring be "rang"
and let the array be
{"apple","orange","lemon"}.
I wish to put a single command line next to my substring and wish to search through that
range and return a key code if it exists. And then I will repeat the same search for other
substrings. For the moment being, I don't care too much about the number of occurences
or the exact cell address where they occur. All I wish to know is whether they exist in that
list or not.

So it looks to me it is like a SEARCH command but except on a range of text (Rather than
a single text) with an array formula. I have tried MATCH but my case is not an exact match
either.

Any help will be greatly appreciated.

Thanks

http://www.mrexcel.com/board/viewtopic.php?topic=8889&forum=2

 

[barry houdini]

You could also use LOOKUP and avoid "array entry", i.e.

=LOOKUP(2^15,SEARCH(Z2,B2:B6),A2:A6)

The difference is that is Z2 is found in more than one of B2:B6 it matches with the last

 

[T. Valko]

Yes that works! Thanks.

That's a clever use of the ISNUMBER function. If I understand how this works correctly, it reduces an array of 5 arbitrary-length strings (32,000+ characters max) to an array of 5 logical values. MATCH then happily returns the row number where TRUE is located in the array. Then index returns the value in the first array corresponding to that row number.

Did I get that right?

SEARCH searches the cells for the substring. If the substring is found in a cell then SEARCH returns the character position of where the substring is located within the larger string. For example:

=SEARCH("xx","try xx for example")

SEARCH returns 5 because the substring "xx" is located starting at character 5 within the larger string "try xx for example".

When the substring is not found SEARCH returns the #VALUE! error.

We get an array of results from the SEARCH function that are then passed to the ISNUMBER function. ISNUMBER tests this array and for those that are numbers like 5, ISNUMBER = TRUE, and for the #VALUE! errors, ISNUMBER = FALSE.

Then we use MATCH to find the relative position of TRUE and this number is then passed to INDEX to return the desired result.

 

[Aladin Akyurek]

When the string length exceed 255 characters, the MATCH function will error out.

For example, let's say I have in column A a list of authors. In column B, I have a quotation from each author that is longer than 255 characters.

In C1, I type a short sub-quote. In D1, I'd like to see which author wrote the text I typed into C1.

The formula entered in D1

{=INDEX(A1:A4,MATCH("*"&C1&"*",B1:B4,0)}

returns #N/A if the string is longer than 255. This is entered with CTRL+SHIFT+ENTER, of course.

I know that this is a fundamental limit on several of Excel's functions. Any ideas on a work-around?

The following would work:

=LOOKUP(9.99999999999999E+307,SEARCH(C1,B1:B4),A1:A4)

which just needs enter.