Search for IP address in one column then pull it out and populate another column

I have an Excel file where one column contains an extended amount of information which includes an IP address. The IP address always starts with "10" but after that it may have 2 or 3 numbers in each remaining octet. For example........ 10.50.220.5 or 10.215.20.200 etc.

I would like to be able to locate the IP address in this column for each row and then pull it out and populate another column. This way I can now sort on the new IP address column and do other things with this information.

This is beyond my Excel knowledge and I don't really have time to research how to do this for days on end.

I am asking for assistance, at least enough so I don't have to spend hours researching how to do this. A nudge in the right direction would help.

Thank you for your time.

Welcome to the forum!

The quickest way to get help is to attach a sample Excel workbook (NOT a picture of one).

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

You said this:

I have an Excel file where one column contains an extended amount of information which includes an IP address.

However, cell B2 in your sample file (which really needs to have some more sample data added - at least 10 rows) has no IP address. Where there is an IP address, is it always preceded by the text string 'IP Address: '?

This MIGHT do it, but with only one row of relevant sample data in your sample file, it probably won't!

=TRIM(MID(A1,FIND("10.",A1),15))

Was the formula in post #5 any good?

When I asked for more data, there was a reason for it. The same data ten times doesn't help, because it doesn't test my formula for different scenarios. What I wanted was ten different sets of data, but all in one format or other found in your real data. Do you understand this?

OK, well my formula works on the sample ... Have you tried it yet on the real data?

Originally Posted by stateworker

Nice, that works so now I will just try to duplicate this logic for each row. I really appreciate your help....

You don't need to! Just paste the formula into B2 and drag copy it down. Do you know how to drag copy a cell's contents?

Enter formula in B1 and copy down

Formula:

Please Login or Register  to view this content.

You're welcome! Let me know how you get on.

In that case, try AlKey's suggestion above.

And if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.