» Sort IP addresses in Excel (2023)

You are amazing, it was a bit difficult because I am not that good at excel. But it worked with some minor glitches for me.
You command

Thanks! I'm glad you found the tutorial useful.

Andres RobertDecember 3, 2013 at 2:31 pm

It's goodExcel spreadsheet toolwhich has the function of converting IP addresses to decimal numbers. Once this is done, sorting is very easy.

Oh, fix D:G, just choose where it goes first and Excel does the rest

Thanks for your comment Andrew. I'm not 100% sure what he meant though, because column F had some content: the location of each computer. You also need the CONCATENATE formula to combine everything so that each IP address is stored in a single cell, not split across 4 cells.

Excellent thanks, the tutorial worked like a charm.

Hi Chris,
I know you're a day late and a dollar short, but the correct thing to do is copy E2 and then drag it to the bottom of the column. Then select Ctrl-D on your keyboard and the rows will automatically update with the appropriate cell numbers.

An alternative method could be to use a "replace find" in the IP address column to remove the decimal places, e.g. find all 10.0.5. and replace the column with 1005. You can then sort the column in standard number format without periods (eg 10051, 10052, etc.) After sorting the column, you can do another search and replace to return to IP format, e.g. 1005 and replace with 10.0.5.

Thanks for a good and accurate tutorial!

Hi Mike, that was a very nice trick for sorting IP addresses. Split data and apply sorting and reintegration. Thanks

Since all of mine were on the same 255.255.255.0 subnet, I added a column for the LEN (IP address) sorted by the one followed by the IP address.

For all you Excel users dealing with IP addresses, there is a good thingExcel add-inwhich locates IP address country, region, city, latitude and longitude, zip code, ISP, domain name, time zone, connection speed, IDD code, area code, weather station code, weather station name, MCC, MNC, mobile brand telephony, height and type of use.

Another easy option is to just use CTRL F to bring up the Find menu, click the Replace tab, and then replace all periods (.) with dashes (-). Then select all data and sort them in ascending order. Once you've sorted them, you can use VLOOKUP on them. When you're done with everything, just replace all dashes (-) with periods (.) and they'll be organized and in the right format.

A great way to deal with difficult IP addresses.
Thank you very much.

This doesn't work for the same reason that having points doesn't work.

well done! This seems the easiest and simplest way...

If you add zeros to the IP addresses, they will be sorted correctly
eg: 192.168.1.1 is rewritten as 192.168.11.001 if they are on the same subnet

Thanks for the great idea.

YOU JUST MADE MY DAY. THANK YOU VERY MUCH…..

Hola,
I need your help to create a perfect formula to create an IP database.
I have a device IP address (for example 192.168.0.49), I need to add another IP address (next hop: device IP address + 1, ie 192.168.0.50).
another example… Device IP: 192.168.0.3, next hop is 192.168.0.4
I need to make a list of 100+ device IP addresses so I thought Excel would be the easiest way.
Gracias

the best answer

Instead of using "=concatenate(A2,B2,C2,D2)", I find it easier to write "=A2&B2&C2&D2". Save some keys...

Concatenation doesn't always work, maybe based on cell format? A better way to concatenate cells back into a string might be: &"."&
=D1&”..”&E1&”..”&F1&”..”&G1

Keep the original data column. Create 4 new columns to the right of the IP address (B, C, D, E). Copy the IP addresses (column A) and paste them into column B. Now go to the table text using column B.