Excel: Retrieve rows based on string search

Hello,

I'm trying to write a fairly simple piece code (or so I thought) to do the following:
In column A on sheet1 in my Excel workbook, I have strings of 5 characters each.
In column A on sheet2 in the same workbook, there are strings of 40 characters each.
I'm trying to retrieve the 40 character strings and put them in column B on sheet1 if this 40 character string contains the 5 character string from sheet1.
It's possible to find more than one 40 character string containing the 5 character string and I would like to show them all in column B on sheet1 per 5 character string.

I hope someone can help me out or at least give me some useful tips.

Thanks in advance.