Excel – correctly sort IP addresses

This post is probably for pedants only, who care passionately about correctly sorting IP addresses in an Excel spreadsheet. This approach uses pure functions – no VBA. I prefer it to some other approaches because, frankly, they sail right over my head.

Let’s start with a column of IP addresses – like this one:

Excel tables are lovely, for working with data like this. If you convert your data to a table, you get to use named column references, which we’ll see in a moment. Go to Insert > Table and you get something like this:

You can’t sort this column meaningfully, as-is. We need an additional column, which we’ll use to transform the contents of the IP column.

And then in any of the rows in that column, we enter this formula:

=
IF(0,"##### FIRST OCTET #####","") &
TEXT(
  LEFT(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    ) - 1
  ),
  "000"
)
& "." &
IF(0,"##### SECOND OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    ) + 1,
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    )
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        1
      )
    )
  ),
  "000"
)
& "." &
IF(0,"##### THIRD OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    ) + 1,
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    )
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        2
      )
    )
  ),
  "000"
)
& "." &
IF(0,"##### FOURTH OCTET #####","") &
TEXT(
  MID(
    [@IP],
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    ) + 1,
    
    IF(
      ISERROR(
        FIND("/",[@IP])
      ),
      LEN([@IP]),
      FIND("/",[@IP]) - 1
    )    
    -
    FIND(
      CHAR(134),
      SUBSTITUTE(
        [@IP],
        ".",
        CHAR(134),
        3
      )
    )
  ),
  "000"
)
&
IF(0,"##### CIDR #####","") &
IF(
  ISERROR(FIND("/",[@IP])),
  "",
  RIGHT(
    [@IP],
    LEN([@IP]) - FIND("/",[@IP]) + 1
  )
)

You end up with this, on which you can now perform an alphabetical (A-Z) sort:

If you like, you can hide that column, so you don’t need to look at its hideousness. Then whenever you need to resort, go to Data > Sort.

Some things to mention about this formula:

• [@IP] is the named column reference I referred to previously.
• I edited this formula in a code editor (Notepad++), so I could nicely indent and keep track of opened and closed parenthesis. This makes life much easier, when writing long formulae! There’s one gotcha – Notepad++ by default uses tabs rather than spaces, which breaks Excel. Make sure there are no tab characters in your indentation.
• The IF(0,"##### THIRD OCTET #####","") stuff is a hack, which allows you to insert a comment into a text-based formula. The 0 evaluates to FALSE, so it returns the function’s third parameter – an empty string. The second parameter is where I place my comment. Handy!
• Excel doesn’t have a function to find the position of the nth occurrence of a string. So there’s a nifty two-step hack for this, which is not my original idea. First, we use the SUBSTITUTE() function, which can substitute a character for the nth occurrence of some text. We search for the nth occurrence of the full stop (“.”) and replace it with CHAR(134) – the dagger symbol (†). Then we find the position of that CHAR(134), to feed into the LEFT()/MID()/RIGHT() functions.
• The formula handles CIDR notation.