Pull data, compile on new worksheet

Hello,

Looking for a little help. Looking to see if this is even a possibility. If it is possible, perhaps pointing me in the right direction, or thread. I have hopelessly tried getting this to work with no luck.

What I'm trying to accomplish....if possible:

I have a workbook with numerous worksheets. I would guess around 100.On every single worksheet in cell G2 there is a bunch of text. I need to pull an IP address from all that text. In Cell C2 there is a Hostname.

Worst case: Macro that I press once, and it adds the formula " =TRIM(MID(SUBSTITUTE(G2," ",REPT(" ",99)),MAX(1,FIND("172.",SUBSTITUTE(G2," ",REPT(" ",99)))-50),99)) " to each worksheet in cell G7. I would still need to click on a bunch of different worksheet and collect the data, but at least it has pulled what I'm looking for from the 2-3 paragraphs of useless information in cell G2.

Best case: Macro I press once, it adds the same formula to each worksheet to cell G7 to pull the information from G2. After which it creates a new worksheet and essentially compiles the data for me. and reference it with what was in cell C2.

So after I'm done I should have a new worksheet that looks like this:

Col A Col B
Hostname1 172.1.2.3
Hostname2 172.3.2.1
Hostname3 172.9.8.7
Hostname4 172.7.8.9
Hostname5 172.4.5.6

Attached sample.

.
This is one way :

Please Login or Register  to view this content.

Thank you both for the help! I think this will do!

.
You are welcome.