» Sort IP addresses in Excel (2023)

You're great, it was a bit difficult because I'm not that proficient with EXCEL. But this worked with a minor glitch for me.
You rock.

Thank you! I'm glad you found the tutorial helpful

André Robergeon December 3, 2013 at 2:31 pm

There is a goodExcel-spreadsheet-toolwhich has a function to convert IP addresses into decimal numbers. Once that's done, sorting is really easy.

oops correct that D:G you just have to select where the first one goes and Excel will do the rest

Thank you or your comment Andrew. I'm not 100% sure what you mean though, as column F had some content - the location of each PC. You also need the CONCATENATE formula to put it all back together so that each IP address is stored in a single cell instead of being split into 4 cells.

Thank you very much, the tutorial worked like a charm.

Hello Chris,
I know this is a day late and a dollar short, but just right and copy E2 and drag the cursor to the bottom of the column. Then select Ctrl-D on the keyboard and the rows will automatically update with the correct cell numbers.

An alternative method could be to use 'find a replacement' on a column of IP addresses to remove the decimals, e.g. find all 10.0.5. and replace with 1005. A sort can then be performed on the column as it is in standard numeric format without the dots (e.g. 10051, 10052 etc.) Once the column is sorted, another find and replace can be performed to return to revert to IP format, e.g. find all 1005 and replace them with 10.0.5.

Thanks for the good and accurate tutorial!

Hi Mike, that was a really nice trick to sort IP addresses. Splitting the data and applying sorting and recombining. Thank you

Since mine were all in the same subnet 255.255.255.0 I added a column for LEN (ipaddress) sorted by that and then by the IP.

For all Excel users dealing with the IP address, one is rightExcel add-inthat detects IP address by country, region, city, latitude, longitude, zip code, ISP, domain name, time zone, connection speed, IDD code, area code, weather station code, weather station name, MCC, MNC, mobile brand name, altitude, and usage type.

Another easy option is to just use CTRL F to open the Find menu, click the Replace tab, and then replace all periods (.) with hyphens (-). Then select all data and sort in ascending order. After they are OK, you can use VLOOKUP on them. After you're done with everything, just replace all the hyphens (-) with periods (.) again and they'll be fine and in the correct format.

An excellent way to deal with difficult IP addresses.
Thank you.

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

Well done! This seems to be the easiest and simplest way possible...

Adding the zeros to the IP addresses sorts them correctly
e.g.: 192.168.1.1 retyped as 192.168.11.001 as long as they are in the same subnet

thanks for a great idea.

YOU MADE MY DAY. THANK YOU MAN…..

Hoi,
I need your help to create an excel formula for creating an IP database.
I have a device IP address (say 192.168.0.49), need to add another IP (next hop: device IP address +1, i.e. 192.168.0.50).
another example... Device IP: 192.168.0.3, the next hop is 192.168.0.4
need to create the list with more than 100 IP addresses of devices, so I think Excel would be an easy way.
Thank you

best answer

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

Merge doesn't always work, maybe based on cell formatting? A better way to concatenate the cells back into one string could be as follows: &”.”&
=D1&”.”&E1&”.”&F1&”.”&G1

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