Microsoft corporation Excel 2013 32/64-b...

Hi all, I found this tread on the forum (http://www.computing.net/answers/office/excel-cell-contains-string-from-range/8984.html) about how to find out if a column in Excel contains part of the string in a given cell. This previous thread deals partially with my question:

Eg. If I have 123456789 in cell A1

and if I have an array B1:B3 containing 345; 543; 354

then I want to find out on which row in B1:B3 there is a string that also occurs in A1.

In this case the formula would return "1" since 345 occurs in A1 and is on the first row of B1:B3The previous thread on this forum only shows how to return FALSE or TRUE if there is such a corresponding string in B1:B3, but somehow I don't get it adapted to what I want, I suppose it has to do with the ctrl+shift+enter thing at the end.

NOTE: my data contains text, I just happened to use only numbers in my simple example.

How would you solve this?

EDIT: I just found that my data contains possible multiple hits:

eg. B1:B3 contains 345 but also 3456, both occur in A1. I only want to find the reference of the longest hit.

message edited by DavidVA

re: I suppose it has to do with the ctrl+shift+enter thing at the endCtrl+Shift+Enter creates an Array Formula. You can do a Google search to learn about the power of Array Formulas, so I won't go into that here.

As for your question, try this array formula which must be entered using Ctrl+Shift+Enter.

=MATCH(FALSE,(ISERROR(FIND($B$1:$B$3,A1))),0)

Paste the formula into a cell and the press Ctrl+Shift+Enter simultaneously to add the { } around the formula. You cannot create an Array Formula just by typing the { } around the formula.

I will break down the formula to show you how it works. You can do this yourself by using the Formula Evaluator in Excel and single stepping through the formula as it evaluates each section from the inside out.

=MATCH(FALSE,(ISERROR(FIND($B$1:$B$3,A1))),0)

FIND($B$1:$B$3,A1)This will return an array that shows the starting location within A1 where each value in B1:B3 was found. If an individual value from B1:B3 is not found, a #VALUE! error will be returned for that value.

In your example, the FIND function will return the following array:

{3;#VALUE!;#VALUE!}

since 345 was found starting at position 3 of the value in A1. None of the other values were found, so they each return a #VALUE! error.

ISERROR(FIND($B$1:$B$3,A1))or

ISERROR({3;#VALUE!;#VALUE!})This will check for errors within the array created by the FIND function and return another array containing TRUE when an error is found and FALSE when there is no error:

{FALSE;TRUE;TRUE} since 3 is not an error while both #VALUE!'s are.

=MATCH(FALSE,(ISERROR(FIND($B$1:$B$3,A1))),0)or

=MATCH(FALSE, {FALSE;TRUE;TRUE},0)This will return the position of FALSE in the array {FALSE;TRUE;TRUE] which is 1.

Please note: The MATCH function is returning the position of the FALSE element of the

,arraynotthe position of 345 in B1:B3. There’s a big difference. I bring this up because if the B1:B3 values are not actually in B1:B3, you will have to adjust for that. For example, if your B1:B3 values were actually in B11:B13, the formula would have to be changed as follows, but would still return 1 because the MATCH function is only looking at thereturned by the ISERROR function. In other words, FALSE would still be found at position 1 of the {FALSE;TRUE;TRUE}array.array=MATCH(FALSE,(ISERROR(FIND($B$11:$B$13,A1))),0)

If you want the formula to return 11, you have to adjust the formula to deal with the fact that the first physical row is 10 "higher" that the first element in the array.

=MATCH(FALSE,(ISERROR(FIND($B$11:$B$13,A1))),0)+10

You must always be aware of the difference between a physical Row or Column and position of an element in an array. They are not always the same value.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

There are often times when a "simple example" leads to a "simple solution". What works for numbers might not work for text. Since your edit related to the length of the (text) string makes the overall task considerably more difficult, please supply a few exact examples of your data.

I would feel more comfortable working on a solution based on real data, not "a simple example".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad03 First of all, thanks for your very helpful reply. It works very well and now i also know what I'm doing.

As for my example, I cannot share my data or specifics of the problem I'm working on since it is not my own "intellectual property", however I found a way around the length problem.

What I did is copy those entries that had longer 'duplicates' and put them in another list. I then let your code search first in the one list and thereafter in the other list (if the first had no result).

For now I am still looking for one small piece in the puzzle.

Some entries have wildcarts, but the 'FIND' function doesn't match them:Eg. I want also 34*7 to be a match, since the string 34(56)7 occurs in A1

Any idea about how to make FIND() wildcart sensitive or another formula that does the trick?

Thanks again

David

Please Ignore that last question, simply replacing it by SEARCH() does the trick ofcourse!

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History