Как отсортировать IP-адреса в Excel’e 

В редакторе MS Excel, вплоть до версии 2008 (для MacOS), отсутствует такой тип данных, как IP-адрес.
При сортировке по возрастанию Эксель выдает нечто такое:

Простым решением этой задачи является перевод по прилагаемой ниже формуле адресов в точечной нотации в адреса в десятичной нотации с последующей их стандартной сортировкой с влиянием на смежные ячейки.

Формула перевода IP-адреса в десятичную форму для англоязычной версии Excel:

=((VALUE(LEFT(B1; FIND("."; B1)-1)))*256^3)+((VALUE(MID(B1; FIND("."; B1)+1; FIND("."; B1; FIND("."; B1)+1)-FIND("."; B1)-1)))*256^2)+((VALUE(MID(B1; FIND("."; B1; FIND("."; B1)+1)+1; FIND("."; B1; FIND("."; B1; FIND("."; B1)+1)+1)-FIND("."; B1; FIND("."; B1)+1)-1)))*256)+(VALUE(RIGHT(B1; LEN(B1)-FIND("."; B1; FIND("."; B1; FIND("."; B1)+1)+1))))

Формула перевода IP-адреса в десятичную форму для русскоязычной версии Excel:

=(((ЗНАЧЕН(ЛЕВСИМВ(B1;ПОИСК(".";B1)-1))))*256^3)+((ЗНАЧЕН(ПСТР(B1; ПОИСК("."; B1)+1; ПОИСК("."; B1; ПОИСК("."; B1)+1)-ПОИСК("."; B1)-1)))*256^2)+((ЗНАЧЕН(ПСТР(B1; ПОИСК("."; B1; ПОИСК("."; B1)+1)+1; ПОИСК("."; B1; ПОИСК("."; B1; ПОИСК("."; B1)+1)+1)-ПОИСК("."; B1; ПОИСК("."; B1)+1)-1)))*256)+(ЗНАЧЕН(ПРАВСИМВ(B1; ДЛСТР(B1)-ПОИСК("."; B1; ПОИСК("."; B1; ПОИСК("."; B1)+1)+1))))

В итоге получаем следующее:

Формула вставлена в ячейку C1.

Написано на основе статьи на mvps.org.

17.09.2009