General discussion

Locked

Excel formula sorting IP Address's

By michael_scott_kelly ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel formula sorting IP Address's

by Shanghai Sam In reply to Excel formula sorting IP ...

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)

Collapse -

Excel formula sorting IP Address's

by michael_scott_kelly In reply to Excel formula sorting IP ...

There isn't always just two numbers. There are more variables than that.

Collapse -

Excel formula sorting IP Address's

by DKlippert In reply to Excel formula sorting IP ...

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

Collapse -

Excel formula sorting IP Address's

by michael_scott_kelly In reply to Excel formula sorting IP ...

Poster rated this answer

Collapse -

Excel formula sorting IP Address's

by michael_scott_kelly In reply to Excel formula sorting IP ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums