Software

Ask something
Discussion Locked

Excel formula sorting IP Address's

0 helpful
5 replies

by michael_scott_kelly

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm working in Excel 97.

I have in column A, the static Ip address's. On another sheet, I'm try to compare the IPs in column A to 4 sets of 8 IP subnets to determine which IP address I want to work with.

I'm trying to do a search on the third period, but I'm not sure how to do it.

eg. Have static IP's of
10.22.35.101
10.64.21.3
10.55.14.25

I have a set to compare against:
10.22.30
10.22.31
10.22.32
10.22.33
10.22.34
10.22.35
10.22.36
10.22.37

In this case the first IP would be valid or true

How do I figure this out?

Thank you

5 replies

by shanghai sam

one way to do it is to seperate the components by creating empty columns to the right of the addresses and then selecting the IP numbers. Go to Data>Text to columns and use a "." as the delimiter.
If there are always two numbers in the first three spots you can use =MID(A1,7,2).
Here's a discussion about IP sorting:

www.geocities.com/davemcritchie/excel/sorttcp.htm

Once again if the numbers are consistent. You could use =VLOOKUP(MID(A1,1,8),CompareList,1,False)

by dklippert

For some reason User Deleted is popping up. The above answer is mine. I hope it comes close to solving your problem.

This discussion is closed to new replies.