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

 

[Aladin Akyurek]

Thank you again Aladin!

You have been most helpful to my project. I really appreciate it.

You are welcome. Thanks for providing feedback.

 

[rcmorris]

=INDEX(A1:A3,MATCH("*"&E1&"*",A1:A3,0))

Can anyone assist in incorporating the above, into the following -

=IFERROR(INDEX($A$1002:$F$1332, SMALL(IF($C$4=$C$1002:$C$1332, ROW($A$1002:$A$1332)-MIN(ROW($A$1002:$A$1332))+1, ""), ROW(A1)),COLUMN(A1)),"")

Right now the value of C4 has to be an exact match to produce multiple results. What I'm after is to produce multiple results from a partial value if exisiting in column C. i.e. SMITH and SMITHSON if "smi" was entered in C4.

Thanks,

r.c.morris

 

[Aladin Akyurek]

Which cell house the current formula?

 

[T. Valko]

Can anyone assist in incorporating the above, into the following -

=IFERROR(INDEX($A$1002:$F$1332, SMALL(IF($C$4=$C$1002:$C$1332, ROW($A$1002:$A$1332)-MIN(ROW($A$1002:$A$1332))+1, ""), ROW(A1)),COLUMN(A1)),"")

Right now the value of C4 has to be an exact match to produce multiple results. What I'm after is to produce multiple results from a partial value if exisiting in column C. i.e. SMITH and SMITHSON if "smi" was entered in C4.

Thanks,

r.c.morris

Change this:

IF($C$4=$C$1002:$C$1332,

To this:

IF(ISNUMBER(SEARCH($C$4,$C$1002:$C$1332)),

 

[rcmorris]

Change this:

IF($C$4=$C$1002:$C$1332,

To this:

IF(ISNUMBER(SEARCH($C$4,$C$1002:$C$1332)),

Thank you very, very much. You've saved quite a bit of frustration. Works perfectly.

r.c. morris

 

[Aladin Akyurek]

Thank you very, very much. You've saved quite a bit of frustration. Works perfectly.

r.c. morris

The formula you have needs editing for efficiency and robustness. That's the very reason of the question in my reply...

 

[T. Valko]

Thank you very, very much. You've saved quite a bit of frustration. Works perfectly.

r.c. morris

You're welcome!

Here's how I would write that formula:

=IFERROR(INDEX($A$1002:$F$1332, SMALL(IF(ISNUMBER(SEARCH($C$4,$C$1002:$C$1332)),ROW($A$1002:$A$1332)),ROWS(A$1:A1))-ROW($A$1002)+1,COLUMNS($A1:$A1)),"")

Note the placement of the expression:

-ROW($A$1002)+1

If we place it outside of the SMALL function as I have then that way we are only calculating a single "offset correction" rather than an array of "offset corrections" which is an inefficient approach used by most people.

 

[ExcelChemist2012]

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?

 

[T. Valko]

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?

Something like this...

Array entered**:

=INDEX(A2:A6,MATCH(TRUE,ISNUMBER(SEARCH("xxxxx",B2:B6)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Where xxxxx is the substring to look for. The substring can also be held in a cell.

Z2 = xxxxx

=INDEX(A2:A6,MATCH(TRUE,ISNUMBER(SEARCH(Z2,B2:B6)),0))

 

[ExcelChemist2012]

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?