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

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

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

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

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

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

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

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

Объяснение формулы:

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

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

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

Для получения второго октета IP-адресов скопируйте или введите следующую формулу в пустую ячейку:

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

Объяснение формулы:

1. FIND(".",A2)+1: Эта функция FIND возвращает позицию первой точки из ячейки A2, добавление 1 означает начало извлечения со следующего символа, результат будет число 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 используется для извлечения 2 символов, начиная с четвертого символа из ячейки 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 извлечет 1 символ, начиная с седьмого символа из ячейки 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 извлечет 3 символа, начиная с девятого символа из ячейки A2.

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

Больше статей: