search for a string in cell having multiple string

i am looking for a code to search for a single cell value in multiple cell value and return the adjacent cell for eg-

in sheet 1-
A B
4F876


Sheet 2-

A B

4F876 4M4646 5GAR 100


Column B in sheet 1 should return 100 value.

Please help.