Solved: Extract and find

[Gil]

I have the following formula which extracts the number 569 from 2085697325. The formula is =MID(B4,FIND("",B4)+3,3). I then need to find 569 in either of 2 cells containing the following 737,847,232,568,255 and 560,587,569,758,326,580. I have tried Find,Match Exact and some others but am now stuck.
The end result I would like to achieve at the moment is to id the cell containing the 569, delete the contents of the other cell,copy the found cell and paste in a new cell.
I hope that makes sense, please see attachment.

[Bob Phillips]

That will all need VBA. Look at Instr in VBA help.

[Gil]

In the long term yes it will be idealy located in VBA. At present I was just trying to build up the formula in cells to see the steps needed to automate all the actions.
I have now put my first step into vba code and attached the file. I just need a little help with my next part which is to id the cell where 569 resides. That is to highlight the cell ready to copy.

[Bob Phillips]

[vba]

Sub Macro2()
Dim cell As Range

Range("E4").Value = Mid$(Range("B4").Value, 4, 3)
For Each cell In Range("I4:J4")

If InStr(cell.Value, Range("E4").Text) > 0 Then

MsgBox cell.Address
Exit For
End If
Next cell

End Sub
[/vba]

[Gil]

Thank you
That was a great reply but instead of the message box can it not just select the cell.
Many thanks

[Bob Phillips]

Sure

[vba]

cell.Select
[/vba]

[Gil]

Many thanks XLD. My task has been resolved.

Cheers Gil