Checking if one or more numbers in a cell can be found in the cell to the right

[Timicheckmate]

Good Afternoon.

I have two cells in excel side by side containing numbers. I want a true response in the third cell (column) if one or more numbers in the first cell can be found in the second cell and a false response if none of the numbers in the first cell appear in the second cell.


Example
01234 278 TRUE
1356 5689 TRUE
37 56 FALSE



Thank you.

[JLGWhiz]

With VBA, but it an probably be done with formulas as well.

Please Login or Register  to view this content.

[Timicheckmate]

Thank you JLGWhiz. The VBA code works perfectly.

[Sam Capricci]

removed, only worked on your examples, when I tried a different last number it failed.

[JLGWhiz]

Thank you JLGWhiz. The VBA code works perfectly.

You're welcome, Don't forget to make the thread as Solved.
Regards, JLG

[GeoffW283]

Here's a formula based solution. It assumes the 2 numbers are in A1 and B1. Enter this formula in C1:

Please Login or Register  to view this content.

This, I think, works for all numbers in A1 with 9 digits or less.

[FlameRetired]

Another one.

With source data in columns A and B try array entering this formula in C1 and fill down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C
1	01234	278	TRUE
2	1356	5689	TRUE
3	37	56	FALSE

[Bo_Ry]

Another one.
C1 Press Ctrl+Shift+Enter

=COUNT(FIND(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),A1))>0

[GeoffW283]

@ Bo, @FlameRetired - are you sure about your solutions? Example:

A1 = 1374, B1 = 278

These numbers have "7" in common so the formula should return TRUE as far as I can see from the OP's requirements. I think you are both generating FALSE. I get TRUE as does JLGWhiz's VBA solution.

---------------------------------------------------------------------------
EDIT: never mind - I missed the fact you both have array formulas. Everything works fine!

Full marks to Bo in particular for another elegant solution.

[Bo_Ry]

Thank, Geoff

My formula is combined your and Dave's formula. I just add COUNT as it counts only numbers and ignores error.

[FlameRetired]

@ GeoffW283

Full marks to Bo in particular for another elegant solution.

I concur.