If you work with a lot of IP addresses, you may want to create a user-defined function that will front-pad each octet of the IP address with zeros and then return a fully formatted IP. When you do your sorting, sort by column B, and the addresses will be in the proper sequence. Place it in the column next to your first IP addresss (assuming that address is in cell A1) and then copy it down as many rows as required. This formula is quite long, but it is still a single formula. If you prefer to use a formula to ensure the front-padding of each octet, you could use the following: This 'front pads' each octet with zeros and, if all of your IP addresses are in this format, they will sort correctly.

(An octet is each part of the IP address, separated by periods.) For instance, instead of an address such as 192.168.1.1, you would use 192.168.001.001. One approach is to make sure that each octet of your IP addresses consist of three digits. You should choose the approach that is right for your needs, as defined by your data and how you use that data. There are a few ways you can work around the problem, a few of which I'll discuss in this tip. This happens because Excel views an IP address as text, not as a number or a series of numbers. Chuck wonders if there is a way to sort a column of IP addresses so they appear in the proper sequence. If he sorts the addresses, they are not numerically sorted.

These are in the familiar format of 192.168.2.1. Chuck has a worksheet that, in one column, contains a series of IP addresses.

Notepad++ sort all lines alphabetically