Разделение октетов IP-адреса в Excel

Пробовали ли вы когда-нибудь разделять IP-адреса на отдельные столбцы в Excel? Возможно, функция «Текст по столбцам» поможет быстро справиться с этой задачей, но в данной статье мы рассмотрим несколько формул для выполнения этой операции в Excel.

• Разделение октетов IP-адресов на отдельные столбцы в Excel

Разделение октетов IP-адресов на отдельные столбцы в Excel

Обычно в Excel функции LEFT, MID, LEN и FIND позволяют легко извлечь каждый октет IP-адреса в отдельные столбцы. Выполните следующие шаги:

Разделение первого октета IP-адресов:

Чтобы извлечь первый октет IP-адреса, примените приведённую ниже формулу в пустую ячейку:

Затем перетащите маркер заполнения вниз до ячеек, к которым нужно применить эту формулу. Все первые октеты IP-адресов будут извлечены (см. скриншот):

Пояснение формулы:

FIND(".",A2)-1: Эта функция FIND возвращает позицию первой точки в ячейке A2, а вычитание единицы исключает саму точку. В результате вы получите число 2.

LEFT(A2,FIND(".",A2)-1)=LEFT(A2, 2): Функция LEFT извлекает два первых символа из строки в ячейке A2.

Разделение второго октета IP-адресов:

Чтобы получить второй октет IP-адресов, скопируйте или введите следующую формулу в пустую ячейку:

Затем перетащите маркер заполнения вниз, чтобы применить формулу к нужным ячейкам, — и вы сразу получите требуемый результат (см. скриншот):

Пояснение формулы:

1. FIND(".",A2)+1: Функция FIND возвращает позицию первой точки в ячейке A2, а прибавление единицы указывает, что извлечение начинается со следующего символа. Вы получите результат 4. Эта часть формулы используется как аргумент start_num функции MID.

2. FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1: Эта часть формулы выступает в качестве аргумента num_chars функции MID.

• FIND(".",A2,FIND(".",A2)+1)= FIND(".",A2,4): Эта функция FIND возвращает позицию второй точки. Вы получите число 6.
• FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1=6-3-1: Вычитание позиции первой точки из позиции второй точки и последующее вычитание 1 позволяет убрать ведущую точку. Результат — 2.

3. MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)=MID(A2, 4, 2):Наконец, с помощью этой функции MID извлекаются два символа, начиная с четвёртого символа ячейки A2.

Разделение третьего октета IP-адресов:

Чтобы извлечь третий октет IP-адреса, можно также использовать функции MID и FIND. Введите или скопируйте следующую формулу в пустую ячейку:

Затем перетащите маркер заполнения вниз, чтобы получить нужные результаты — будет извлечён только третий октет IP-адресов (см. скриншот):

Пояснение формулы:

1. FIND(".",A2,FIND(".",A2)+1)+1:Эта вложенная функция FIND определяет позицию второй точки в ячейке A2, а добавление 1 указывает, что извлечение текста начинается со следующего символа. В результате вы получите число 7. Это значение используется как аргумент start_num для функции MID.

2. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1): Эта часть формулы выступает в качестве аргумента num_chars функции MID.

• FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)=FIND(".",A2, 7): Эта вложенная функция FIND определяет позицию третьей точки в ячейке A2 и возвращает число 8.
• FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1)=8-7: Вычитание позиции второй точки из позиции третьей точки даёт результат 1.

3.MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 7, 1): Эта функция MID извлекает один символ, начиная с седьмого символа в ячейке A2.

Разделение четвёртого октета IP-адресов:

Приведённая ниже формула поможет вам извлечь последний октет IP-адреса. Скопируйте её или введите в пустую ячейку:

Затем перетащите маркер заполнения, чтобы применить эту формулу ко всем нужным ячейкам, — и вы получите результат, как на скриншоте ниже:

Пояснение формулы:

1. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1: Эта вложенная функция FIND определяет позицию третьей точки в ячейке A2; прибавление 1 указывает, что извлечение начинается со следующего символа. В результате вы получите число 9. Эта часть служит аргументом start_num для функции MID.

2. LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1):

• LEN(A2): Функция LEN возвращает общее количество символов в ячейке A2 — вы получите число 11.
• LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)=11-8: Общая длина содержимого ячейки A2 за вычетом позиции третьей точки даёт количество оставшихся символов. В результате получится число 3.

3. MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 9, 3):Наконец, эта функция MID извлекает три символа, начиная с девятого символа в ячейке A2.

Используемые связанные функции:

Дополнительные статьи: