Seperating and Filtering IP Addresses in Excel

Please define how (you would like) to determine:

a) IPv6 Addresses
b) IPv4 Addresses
c) Internal Addresses
d) External Addresses

Also, providing some further sample input, & the corresponding output other than that which you have already mentioned above, will be helpful, as the example provided makes little sense (from the information in your question).

Input:
10.7.0.200,
0.23.25.155,
10.23.91.56,
91.202.169.245,
91.202.169.45

Output:
[10.7.0.200,10.23.35.155,10.23.91.56] [3] <- 3 of which type?
[91.202.169.245,91.202.169.45] [2] <- 2 of which type?

Thank you.

Can you upload a sample file with a few lines of addresses showing the required result. Also an explanation of how to differentiate between IPv6, IPv4, Internal Addresses & External Addresses would be helpful.

Hi, Sorry, Let me clarify..
and IPv4 Address is defined as an address made of four numbers (between 1 and 255) divided by dots. (i.e X.X.X.X Where X is anywhere between 1 and 255 in each and every one) everything that does not conform to this structure is not IPv4.
As for distinguishing between internal and external addresses.. that's a bit more tricky, but essentially anything that's in the following format is internal:
10.X.X.X
192.168.X.X
and the following range as well: 172.16-31.X.X (meaning the 2nd number can be anything between 16-31)
in all the cases X is anything between 1 and 255.
I hope this makes sense

Thanks.

Are you absolutely sure that none of the numbers in the number-dotted arrangement can be 0?  That is, 0 to 255, not 1 to 255, as you have stated above.

...or is this something that can be discounted just for your purposes?

Additionally, would any addresses including a number outside of the range (1..255, or 0...255, depending on your response to my above query), be considered 'invalid', & should be ignored from the results?

Finally, are you planning to upload a sample file, like Syed suggested?

Hi,
0-255 isn't a problem and actually I should've been more percise, 0 is possible but only in the 2nd and third numbers, the first and last numbers will never contain that number - it simply can't.. in the world of networking it's considered an invalid address, but since I'm pulling the information from a production environment, all the IPs are valid for certain, so we can safely make the search parameter 0-255 if it makes it easier for you
Regarding a sample file, It might take me some time to make one, as I'm not in front of my computer and won't be for a couple of hours.

Hi David,

Please find the attached workbook with two UDF's I used long time ago for almost the same task.
In the attached workbook, on Module1, you will find two UDF's InternalIP() and ExternalIP() which are used in B1 and D1 on Sheet1.

See if these udf's are helpful to you also.
IP-Addresses.xlsm

That's exactly what I needed in terms of screening out internal and external, perfectly designed!
The only problem is, the catches IPv6 Addresses as external ones
Any way to scrren them out before applying the UDFs? or make the screening part of the UDF?

I think I didn't get your question correctly.
You haven't provided any details of IPv6, do you want to exclude them from the output?
It would be helpful if you upload the workbook back with the desired output.

Also please edit your question to add the proper topics.

Apologies, Yes, I need the file to completely ignore IPv6 Addresses.
Also, for some reason when I tested a case when the order was not explicit but mixed (internal, then external and then another internal) the external address count was wrong, it was larger by one.
I added a file with two tabs, the first is the output made by the functions you provided, the other is the desired result.
IP-Addresses.xlsm

Okay give me some time as I am going to take a break. I will look at your file once I am back.

I got here too late :-(
I was going to go the regexp solution route, like sktneer.

That one did the trick, Perfect code, Thank you very much!

All suggestions within this thread will "do the trick".

This will be my first & my last response to you, David; if you do not even acknowledge my proposal to address your question.

I expect sktneer feels equally slighted, although, to be fair, you did actually look at his suggestion.

@fanpages

Thanks for the insight. :)

Actually David rewarded aikimark for getting here late. lol
This also shows he is not capable of recognizing the help correctly.

Honestly I list all such askers in the Ignore List and don't prefer to answer them in their future questions. :)

No problem, sktneer.

Also, for the record, I hold no grudges or issues with you, aikimark.

Have a good day everybody.

Also, for the record, I hold no grudges or issues with you, aikimark.

Me too. :)

@David Sankovsky

Did you mean to accept my comment as the only solution to your question?  My comment was not the first you were testing and was meant to offer later readers more regexp examples.

We can reopen the question, allowing you to accept multiple comments.

I am trying, I've been trying ever since I accidentally closed it only on your solution.. but I see no "edit" or "reopen" button anywhere..

Thanks! I was trying to edit this for ages.

Sorry David.

Accepting my (or, indeed, anybody else's) solution was not the motive behind my comment earlier.

Thank you for progressing with closure again though.

My point was that it struck me as rude that you did not even acknowledge my proposal, not even a "Thanks, but no thanks".

It was far from a trivial amount of effort, especially ensuring I commented the lines so you could follow them if your requirements ever changed in the future.

I appreciate you coming back & closing, but what solution did you actually progress with using?

Hi [ fanpages ],

I eventually used aikimark's solution for several reasons.
It was divided into several sub-functions that were actually easier to follow and easier to integrate into the main script I'm running (The action of dividing the list into internal and external is only part of what I'm eventually trying to achieve). Further, your subroutine, while working absolutely  correctly, can't be easily modified by someone who isn't proficient in VBA programming, and is limited to a certain workbook, while several smaller functions, can be called anywhere that I might need provide much greater flexibility.

As for sktneer's code, while indeed using functions, they couldn't correctly deal with mixed lists (where the addresses weren't arranged as internal first and external later), once he had fixed this issue, the function he used to count the addresses in each instance were always 1 higher or 1 lower than the actual result.

aikimark's solution was simply the best suited for the task at hand.

Hi David,

That is indeed a good reason to use that code.

I am not sure why you believe my proposal could not be ported elsewhere, but obviously none of us were aware that this would be your ultimate goal when you stated your requirements.

I was coming from an angle where, if you had many, many numbers of rows of data, calling four separate functions may hinder performance, so I opted to process all the necessary output at once.


Everybody has different strengths & weaknesses, & you may find it easier to read "regular expression" syntax versus Visual Basic for Applications code (regardless of the comments I added).

No need to dwell on this though.   I'm not here for points, I'm just here to help.

If you do feel you need to return to one/other solution in the future, please just comment again (or raise a new question referencing this one).

Thanks again for taking the time to participate, & to explain your decision :)

@David

Thanks for the points, sticking with the question, and the multi-accept closure.

==============
@sktneer

This also shows he is not capable of recognizing the help correctly.

Please understand that David has only been an EE member since February and this is only his 26th question.  Try to remember your learning curve and work with newbies, like David, to get them up to speed.  If it makes you feel any better, I started with your VBA code and your regex patterns.

You and fanpages did great analytical work, extracting the detailed requirements to David's problems.  The two of you deserve points for that part of the solution, not just my VBA code.  The great thing about places like EE is that wonderful solutions can come from collaboration like we see in this thread.

In a way, I'm kind of like BASF.  I make things better  :-)

(cough)

Thanks for your help too, Syed :)

@aikimark

Point noted.  :)

But see what fanpages said and I echo that.

My point was that it struck me as rude that you did not even acknowledge my proposal, not even a "Thanks, but no thanks".

oops.  No slight, Syed.

==================
@fanpages

good catch.

is a 'cough' the same as a 'harrumph'?

Hi, all. I was just enjoying recalling the past such experiences of the frustration when your effort goes "unnoticed".

I have had cases where evidently the asker went with my solution but awarded the points to someone else. And I must admit that I am one of those who are "working for points".