Possibly the easiest, fastest way to sort IP addresses in Excel 2013
My method to quickly and EASILY sort IP addresses in Excel 2013
Want a quick, easy way to sort 254 IP addresses?
You can do this just using half a dozen clicks and entering two ip addresses!
This is EASY
If like me, you have created an Excel spreadsheet detailing computer equipment and IP Addresses, you will have discovered that the IP addresses are not sorted correctly.
The issue arises because the IP addresses in the spreadsheet are treated as text, and therefore are not sorted correctly. Whilst you can search online for various ways to sort the IP addreses, they often are complicated (including splitting the address into four columns and using concatenation) when all someone wants is just a quick fix.
Development
Using Excel 2013, I started out finding a better solution by re-typing the last digits of the address in a new SORTED column and sorting them by this new column, then realised that I only need to type the end of the first IP address, because when you get to the second one it automatically gives you the whole lot!
Summary of solution
You can sort or edit as usual
Give it a try. You will discover that if you do not exactly follow the instructions here you might get strange results. but you’ll soon get the feel of it, discover any limitations and be able to adjust it to taste.
Here is the problem and solution step by step..
When you sort typical IP address in Excel, they are ‘not completely’ sorted correctly.
Example of the issue:
I show a fictitious but typical example below. You can see I have put a red circle around the addresses that do not really seem to be in the right order
The fast, easy solution
Step 1.
In this typical list in your Excel 2013 spreadsheet, insert a new column to the left of the IP addresses.
Step 2
Just to make things clear, I have labelled this column ‘sorted‘. You don’t need to do this though.
Go carefully with this one. If it doesn’t work, go back to step 1.
Firstly, you must now enter just the final digits of the first device’s IP address into the new Sorted column.
Step 3
STOP, GO CAREFULLY.
(You can see in my example, the second device is 192.168.1.100. The last digits of the second device IP address is 100. )
This is the main element of my instructions. It produces a ‘sorted’ list with no effort.
You are nearly done! But if you don’t get this happening and it doesn’t work, go back to step 1.
Now that you can see these numbers appearing in grey in the Sorted column, simply press the Enter key so they turn to black.
The final step.
That’s my revelation completed.
Note: If you don’t know how to do this, just click once on the ‘Sorted’ column header,
Select DATA from the tab headings at the top of Excel, click on the Sort icon in the toolbar.
Choose to sort by the ‘Sorted’ column. usually that’s Column A.
You are done!
RESULT!!
Yes, you are all finished.
If you wish, you can delete the temporary ‘sorted’ column.
I hope you found this helpful!
Sorting IP addresses; Sort IP address in Excel; IP address in wrong order ; IP address sort ; addresses in wrong order ;
Report abuse
Was this discussion helpful?
Sorry this didn’t help.
Great! Thanks for your feedback.
How satisfied are you with this discussion?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this discussion?
Как получить ячейки в Excel, которые содержат IP-адреса для правильной сортировки?
В настоящее время я работаю с большим списком IP-адресов (их тысячи).
Однако, когда я сортирую столбец, содержащий IP-адреса, они не сортируются интуитивно понятным или простым способом.
Например, если я введу IP-адреса следующим образом:
И тогда, если я сортирую в порядке возрастания, я получаю это:
Можно ли отформатировать ячейки так, чтобы, например, IP-адрес 17.255.253.65 появлялся после 1.128.96.254 и до 103.236.162.56 при сортировке в порядке возрастания?
Если нет, есть ли другой способ для меня достичь этой конечной цели?
Как вы, возможно, поняли, ваши IP-адреса рассматриваются как текст, а не как числа. Они сортируются как текстовые, что означает, что адреса, начинающиеся с «162», будут предшествовать адресам, начинающимся с «20». (потому что символ «1» предшествует символу «2».
Вы можете использовать формулу, представленную в этом ответе: https://stackoverflow.com/a/31615838/4424957, чтобы разделить IP-адрес на его части.
Если ваши IP-адреса находятся в столбцах A, добавьте столбцы BE, как показано ниже.
в ячейке B2 и скопируйте его в столбцы BE во всех строках, чтобы получить четыре части каждого IP-адреса. Теперь рассортируйте весь диапазон по столбцам от B до E (в указанном порядке), как показано ниже:
Если вы не хотите видеть вспомогательные столбцы (BE), вы можете их скрыть.
В соседней колонке напишите эту формулу
= СЦЕПИТЬ (В3, «», С3, «», D3, «», Е3)
Наконец сортировка в порядке возрастания.
Проверьте снимок экрана.
зеленый после применения текста к столбцу (столбец от B до E).
черныйПосле нанесения цвета происходит конкатенация и сортировка (столбец F).
Причина заключается в том, что изначально IP-адрес очень прост: текстовые данные, и Excel не принимает формат ячейки, чтобы превратить его в номер.
Надеюсь, это поможет вам.
Вот функция VBA, которую я написал некоторое время назад для решения той же проблемы. Он генерирует версию IPv4-адреса с добавками, которая сортируется правильно.
Простой пример:
Вы можете отсортировать по столбцу «Сортируемый» и скрыть его.
Вот ответ, который займет только 1 столбец вашей таблицы и преобразует адрес IPv4 в нумерацию с основанием 10.
Поскольку вы помещаете свои данные в столбец «M», это начинается в ячейке M2 (метка M1). Инкапсуляция в виде кода дает один ужасный беспорядок, поэтому я использовал blockquote:
Не совсем легко читаемая формула, но вы можете просто скопировать и вставить в свою ячейку (предпочтительно N2 или что-то еще в той же строке, что и ваш первый IP-адрес). Это предполагает правильное форматирование IP-адреса, так как исправление ошибок в формуле сделает его еще хуже при разборе человеком.
Если вы не хотите использовать формулы или VBA, используйте Power Query. (В Excel 2016, Get & Transform, в Excel 2010 или 2013 установите надстройку PowerQuery, чтобы следовать ей).
Это похожая строка, которая преобразует октеты в 3-значные поля, что позволяет выполнять надлежащую сортировку.
Как показано в вопросе, в столбце M указаны IP-адреса (IPv4), начиная с M2.
Получив хорошие ответы от каждого, вот мое решение. Требуется только 1 вспомогательный столбец. Мы пытаемся отформатировать адреса IPv4 в 012.198.043.009 формат, а затем отсортировать их:
Сортировать по столбцу N
Explaination
Помогите с MS Excel
18 лет на сайте
пользователь #7387
И кстати вопрос. Как в Excel отсортировать список IP-адресов?
18 лет на сайте
пользователь #4604
Разделить каждый айпишник на 4-е ячейки (Data \ Text to Colums). Отсортировать в нужном порядке. При желании слепить назад. Если сортировка нужна на регулярной основе, то резать IP на составляющие лучше формулой MID.
17 лет на сайте
пользователь #15360
Разделить кажрый айпишник на 4-е яцейки (Data \ Text to Colums). Отсортировать в нужном порядке. При желании слепить назад. Если сортировка нужна на регулярной основе, то резать IP на составляющие лучше формулой MID.
Yuri K., так вырезание четвертой части с помощью функции ПСТР, представляет собой очень огромную формулу, так как приходится учитывать что в первых трех частях может быть от 1 до 3 цифр. Или ты знаешь красивое решение?
18 лет на сайте
пользователь #7387
Разделить кажрый айпишник на 4-е яцейки (Data \ Text to Colums). Отсортировать в нужном порядке.
18 лет на сайте
пользователь #4604
Yuri K., так вырезание четвертой части с помощью функции ПСТР, представляет собой очень огромную формулу, так как приходится учитывать что в первых трех частях может быть от 1 до 3 цифр. Или ты знаешь красивое решение?
Обожаю нетривиальные решения.
Для ячейки A1 универсальня формула по «откусыванию» последней части IP (1,2 или 3 знака в конце и неважно что там впереди) может выглядеть так:
На изящество и красоту не претендую, давайте спортивного интереса ради поищем более красивую реализацию.
А вообще, если не сводить задачу к откусыванию именно последней части IP, а просто разложить айпишник по столбцам, то все сводиться к 2-м промежуточным ячейкам и 2-м простым формулам
откусываем первую часть до точки
в промежуточной ячейке достраиваем айпишник без первого куска, чтобы от него потом отпять откусить 1-й формулой:
Получается 2-а столбца с промежуточными результатами, зато относительно просто и наглядно.
17 лет на сайте
пользователь #15360
Yuri K., так вырезание четвертой части с помощью функции ПСТР, представляет собой очень огромную формулу, так как приходится учитывать что в первых трех частях может быть от 1 до 3 цифр. Или ты знаешь красивое решение?
Обожаю нетривиальные решения.
Для ячейки A1 универсальня формула по «откусыванию» последней части IP (1,2 или 3 знака в конце и неважно что там впереди) может выглядеть так:
=IF(ISERROR(FIND(«.»;RIGHT(A1;3:);RIGHT(A1;3);IF(ISERROR(FIND(«.»;RIGHT(A1;2:);RIGHT(A1;2);RIGHT(A1;1
На изящество и красоту не претендую, давайте спортивного интереса ради поищем более красивую реализацию.
Продолжим научную дисскусию.
Вопрос по твое формуле: Зачем в формуле двоеточие?
Кроме того полученный текст надо перевести в число.
Кстати, перевел ее на русский.
А ради спортивного интереса, толко что открыл для себя новую функцию ПОДСТАВИТЬ() и спомощью ее предлагаю вариант решения
Excel сортировка ip адресов
Как заполнить IP-адрес с приращением в Excel?
Иногда вам может потребоваться назначить IP-адрес своим коллегам, и диапазон IP-адресов может быть, например, от 192.168.1.1 до 192.168.10.1. Что вы можете сделать, чтобы их было легко создать? Собственно, функция Auto Fill с этим работать не может, или вы можете просто создать их вручную, вводя их в ячейки одну за другой? В этой статье будут рекомендованы два метода для заполнения IP-адреса с приращением в Excel.
Если вы хотите сгенерировать диапазон IP-адресов от 192.168.1.1 до 192.168.10.1 (номер приращения находится в третьем октете), вам поможет следующая формула.
1. Выберите пустую ячейку (говорит ячейка B2), введите в нее приведенную ниже формулу и нажмите Enter ключ.
=»192.168.»&ROWS($A$1:A1)&».1″
2. Затем вы увидите, что первый IP-адрес создан, выберите эту ячейку, перетащите ее дескриптор заполнения в ячейку, пока не будут созданы все необходимые IP-адреса.
Заметки:
Если приведенные выше формулы трудно запомнить, Вставить порядковый номер полезности Kutools for Excel может помочь вам легко решить эту проблему.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутоос > Вставить > Вставить порядковый номер. Смотрите скриншот:
2. в Вставить порядковый номер диалоговое окно, вам необходимо:
3. Если вам нужно создать IP-адреса с указанным приращением, выберите диапазон ячеек, в котором вы хотите найти IP-адреса, затем выберите это правило IP-адреса в Вставить порядковый номер диалоговое окно и, наконец, щелкните Диапазон заполнения кнопка. Смотрите скриншот:
Вы также можете использовать эту утилиту для генерации номера счета-фактуры.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Sorting IP Addresses
by Allen Wyatt
(last updated January 28, 2021)
Chuck has a worksheet that, in one column, contains a series of IP addresses. These are in the familiar format of 192.168.2.1. If he sorts the addresses, they are not numerically sorted. For instance, Excel places 192.168.1.100 between 192.168.1.1 and 192.168.1.2. Chuck wonders if there is a way to sort a column of IP addresses so they appear in the proper sequence.
This happens because Excel views an IP address as text, not as a number or a series of numbers. There are a few ways you can work around the problem, a few of which I’ll discuss in this tip. You should choose the approach that is right for your needs, as defined by your data and how you use that data.
One approach is to make sure that each octet of your IP addresses consist of three digits. (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. This «front pads» each octet with zeros and, if all of your IP addresses are in this format, they will sort correctly.
If you prefer to use a formula to ensure the front-padding of each octet, you could use the following:
This formula is quite long, but it is still a single formula. 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. When you do your sorting, sort by column B, and the addresses will be in the proper sequence.
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. The following will perform the task:
In Excel, then, you could use the UDF in this manner, assuming your original IP address in in cell A1:
Another approach is to simply divide the IP addresses into separate columns, putting each octet in its own column. This is easy to do if you use the Text to Columns tool, in this manner:
Figure 1. The beginning of the Convert Text to Columns Wizard.
Once done, you can sort the four columns as you would normally sort numbers. Then, when you want to put the IP addresses back together, you could use a formula such as this:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13481) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen.
MORE FROM ALLEN
Specifying Your Target Monitor
Custom Formats for Scientific Notation
Changing Currency Formatting for a Single Workbook
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
More ExcelTips (ribbon)
Sorting while Ignoring Leading Characters
Sorting an Entire List
Need to sort all the data in a table? Here’s the fastest and easiest way to do it.
Sorting for a Walking Tour
Subscribe
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click «Subscribe.»
Comments
After separating IPV4Addresses, with «Text to column» delimiter by «.», Sort by Column A, then B, then C, etc.
Then reorg with.
=concat(A1,».»,B1,».»,C1,».»,D1)
NOTE: To get the /24 off the end of an address with the mask, like 10.1.1.0/24, you have to delimit again by «/» and then the process becomes two-step with an extra step to add the forward slash (/) back after the prior step, all to another column. Then copy and paste to a column as text, to preserve as text.
Note: All the other options with text formula are very long BS.
-Have fun!
A different approach to the helper column would be:
=TEXTJOIN(«»,FALSE,TEXT(FILTERXML(» «&SUBSTITUTE(C1,».»,» «)&» «,»//Group/Members»),»000″))
It uses the FILTERXML() approach for splitting data (that takes up most the line as the «tags» I chose for clarity are not single characters meant to shorten it). Then TEXT() to achieve the three character sets for sorting. Finally, TEXTJOIN() puts it all back together in a single cell.
It will handle any length string that is divided by the «.» so IPV4 and IPV6 both come out just fine.
It is text but that doesn’t matter as the pieces were padded to three characters so an «alphabetical» (or «A-Z») type sort works perfectly. And the padding, and not putting the «.» characters back in, doesn’t matter because it’s the original column that is «human readable» to borrow from the barcoding world.
Sorting dates using «YYYYMMDD» works perfectly alphabetically as well for the same kind of reason as here.
However, the above approach does not lend itself to SPILL functionality as TEXTJOIN() puts EVERYTHING together so a range inside the SUBSTITUTE() will get one (very) long string, not a column of values to sort on.
Here’s the simplest formula I’ve come across that works well. This creates a simple numeric value in separate column that you can sort on. No need to modify the IP address in any way.
Assuming your IP Address is in Cell A2, Use this formula in a separate column and sort on it.
=VALUE( LEFT(SUBSTITUTE(A2, «.», » «), 3 ))*2^24
+ VALUE( MID(SUBSTITUTE(A2, «.», » «), 8, 5 ))*2^16
+ VALUE( MID(SUBSTITUTE(A2, «.», » «), 15, 7))*2^8
+ VALUE(RIGHT(SUBSTITUTE(A2, «.», » «), 3 ))
Function VBTEXTJOIN(ByVal ref As Range, _
Optional separator As String = «, «, _
Optional include_blanks As Boolean = False, _
Optional Order As Integer = 0) As String
Dim c As Range, str As String, x As Integer
So to concatenate D2:G2 you would enter VBTEXTJOIN(D2:G2,».»)
@Bill A
You even don’t need CONCATENATE. This is shorter:
Padding with 0’s is a terrible idea! If you do a lookup on an address with padded 0’s, it is converted to the hex equivalent.
Puts your IP address back together
Allen, this was super helpful, thanks for all the details. The Macro worked perfectly.
Here’s my solution (uses Excel and Notepad++):
Paste your list of IP addresses on line 2 in any column.
Select the column.
Go to the Data tab.
Click Text to Columns.
Click Delimited.
Click Next.
Check «Others» and enter a period.
Click Finish.
Now your IP addresses will be split across four columns.
Highlight those four columns.
Go to the Home tab.
Click Sort & Filter.
Click Filter.
Now line 1 will have a drop-down sort menu for each column.
Start at the 4th column, click the drop-down menu. Sort Smallest to Largest
Repeat for the 3rd column, then the 2nd, then the first.
All of your IP addresses are now sorted.
Highlight all of the IP addresses and copy.
Paste into Notepad++
Double click on the space between two numbers (where a period should go)
Press CTRL F
Click the Replace tab
«Find what» should be filled with the space you double clicked.
Click «Replace with» and type in a period.
Click Replace All.
Your sorted IP addresses are now recombined.
In Excel, you can disable Text to Columns by highlighting a column, Clicking Text to Columns in the Data tab, click Next with Delimiter marked, uncheck all delimiter boxes and pressing Finish.
Now you can safely paste the sorted IP addresses back into Excel.
Once you get the hang of it you can sort any number of IPs in about 30 seconds.
This could be faster with a way to recombine the IPs within Excel, but I don’t know how.
