Match Column A with Column B, return Column A (harder than it looks - column B needs number extraction?)

[NikolB]

I have 3 Columns:

Column A I've widdled down to solely invoice numbers, 6 or 7 digits in length, all begin with 1, although there are some zeros

I.E.:
135678
1803708
121943
0
131251


Column B is an employee populated field where they write things like "This is my Jan 25 invoice 135678 for this business"
no position on number, etc, totally random. AND, there will be other numbers too like telephone numbers or dates, none 6-7 numbers long

Column C is the employee name who made the entry


I am hoping to create a formula that will highlight when the number from column A appears anywhere in column B: when it does, turn the row Green, when it does not, turn Red
or any better ideas? I just need a sense of who is writing their invoices in the titles and who is just writing words. The part I'm stuck on is extracting, or finding, the number in the text string.

Thanks for any thoughts!!

 

[jtakw]

Hi,

Use D2 formula for CF rule, format Green.
Use E2 formula for CF rule, format Red.

Book1
	A	B	C	D	E
2	135678	This is my Jan 25 invoice 135678 for this business	John Doe	TRUE	FALSE
3	1803708	1803708 is my Jan 25 invoice for this business	John Doe	TRUE	FALSE
4	121943	This is my Jan 25 invoice for this business call me at 1234567890	John Doe	FALSE	TRUE
5	0	This is my Jan 25 invoice 135678 for this business	John Doe	FALSE	TRUE
6	131251	This is my Jan 25 invoice for this business 131251	John Doe	TRUE	FALSE
Sheet231

Cell Formulas
Range	Formula
D2	=ISNUMBER(FIND(" "&$A2&" "," "&$B2&" "))
E2	=NOT(ISNUMBER(FIND(" "&$A2&" "," "&$B2&" ")))

Don't know what you want done when Column A is 0 (zero)

 

[NikolB]

Wow that was quick! Ok getting closer, but my invoices (column A) can be anywhere within B - not necessarily check B1 for the value in A1

 

[jtakw]

Wow that was quick! Ok getting closer, but my invoices (column A) can be anywhere within B - not necessarily check B1 for the value in A1

Then you probably don't mean this neither

when it does, turn the row Green,

Book1
	A	B	C	D	E
2	135678	This is my Jan 25 invoice for this business	John Doe	FALSE	TRUE
3	1803708	This is my Jan 25 invoice for this business 131251	John Doe	TRUE	FALSE
4	121943	This is my Jan 25 invoice for this business call me at 1234567890	John Doe	FALSE	TRUE
5	0	This is my Jan 25 invoice 135678 for this business	John Doe	TRUE	FALSE
6	131251	1803708 is my Jan 25 invoice for this business	John Doe	TRUE	FALSE
Sheet231

Cell Formulas
Range	Formula
D2	=ISNUMBER(LOOKUP(2,1/SEARCH(" "&$A$2:$A$6&" "," "&$B2&" ")))
E2	=NOT(ISNUMBER(LOOKUP(2,1/SEARCH(" "&$A$2:$A$6&" "," "&$B2&" "))))

D2 for Green.
E2 for Red.