Excel IP Address Parse and Search

[birdman91]

Hello All,

I've been lurking through these forums in search of a result for my problem and could not find a complete solution. So I made an account to see if someone out there can help me out.

I have a word document that I get regularly that contains hundreds of IP address along with text in this general Format:

We are looking at these IP address 122.122.121.123 and 12.13.51.123 with special attention to the following:
A. 187.213.123.1
B. 232.123.54.012
C. etc
D. etc...

1. 124.123.123.123
2. 18.12.51.32
3. etc....

Right now I am copying and pasting the word document into excel and running the following

Sub removechar()
Dim rng as range
Dim workrng as range
on error resume next
xtitleid = "IPaddress"
set workrng = application.selection
set workrng = application.inputbox("range", xtitleid, working.address, type:=8)
For Each rng in workrng
     xout = ""
     for i = 1 to len(rng.value)
            xtemp = mid(rng.value, i, 1)
            if xtemp like "[0-9.]" then
                xstr = xtemp
            else
                xstr = ""
            end if 
            xout = xout & xstr
      next i 
      rng.value = xout
next
end sub

As you can see it is simply removing everything that is not a number or a period. So as you can imagine I get the following outputs.

122.122.121.12312.13.51.123
.187.213.123.1
.232.123.54.012
.
....

1.124.123.123.123
2.18.12.51.32
3.....

With this I can go through and manually clean up the rows but when I get thousands of rows sometimes it can get tedious. The obvious output I'd like to see is... which deletes empty rows as well.
122.122.121.12
312.13.51.123
187.213.123.1
232.123.54.012
124.123.123.123
18.12.51.32

The next step is where it gets even trickier. I want to be able to cross check those IPs versus a master list to check for duplicates. Now I know I can use conditional formatting to easily highlight duplicates but I also need to check against IP ranges in this format.

[TABLE="width: 500"]
<tbody>[TR]
[TD]2.0.0.0[/TD]
[TD]2.255.255.255[/TD]
[/TR]
[TR]
[TD]15.42.32.0[/TD]
[TD]15.42.40.255[/TD]
[/TR]
[TR]
[TD]145.132.123.0[/TD]
[TD]145.140.255.255[/TD]
[/TR]
</tbody>[/TABLE]


My ideal output and final check workbook, if even possible, would look something like this with the Check IP denoting if it is a duplicate value by highlighting itself or other visual queue.[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Check IPs[/TD]
[TD]Master Single IP list[/TD]
[TD]Master IP Range List[/TD]
[TD]Master IP Range list[/TD]
[/TR]
[TR]
[TD]122.122.121.12[/TD]
[TD]7.0.0.0[/TD]
[TD]2.0.0.0[/TD]
[TD]2.255.255.255[/TD]
[/TR]
[TR]
[TD]312.13.51.123[/TD]
[TD]7.0.0.1[/TD]
[TD]15.42.32.0[/TD]
[TD]15.42.40.255[/TD]
[/TR]
[TR]
[TD]187.213.123.1[/TD]
[TD]7.0.0.2[/TD]
[TD]145.132.123.0[/TD]
[TD]145.140.255.255[/TD]
[/TR]
[TR]
[TD]232.123.54.012[/TD]
[TD]7.0.0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124.123.123.123[/TD]
[TD]7.0.0.4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18.12.51.32[/TD]
[TD]7.0.0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where the values in the Check IP Column can be changed fluidly but is checked against the "Master Single IP list" and against all IPs within the ranges of "Master IP Range List"

Any attempts would need to be able to scale to be able to handle thousands of IPs if possible...

I know this is asking a lot, especially for a brand new member, but this would be a great way to streamline a tedious task I do daily...

Thank You in Advance.

 

[birdman91]

You are by far the greatest person I've ever met on the internet haha. Thank you so much!