Перейти до основного матеріалу

Окремі октети IP-адреси в Excel

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


Розділіть октети IP-адрес на окремі стовпці в Excel

Зазвичай в Excel функції LEFT, MID, LEN та FIND можуть допомогти вам витягти кожен октет з IP-адреси в різні стовпці. Будь ласка, виконайте такі дії:

Розділіть перший октет IP-адрес:

Щоб витягти перший октет IP-адреси, застосуйте формулу нижче в порожню комірку:

=LEFT(A2,FIND(".",A2)-1)

А потім перетягніть маркер заповнення до комірок, до яких ви хочете застосувати цю формулу, і всі перші октети IP-адрес були витягнуті, див. Знімок екрана:

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

ЗНАЙТИ (".", A2) -1: Ця функція FIND повертає позицію першої крапки з клітинки A2, віднімаючи 1 означає виключити саму крапку, ви отримаєте результат 2.

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


Розділіть другий октет IP-адрес:

Щоб отримати другий октет IP-адрес, скопіюйте або введіть наступну формулу в порожню комірку:

=MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)

Потім перетягніть маркер заповнення до комірок, щоб заповнити цю формулу, і ви отримаєте результат, як вам потрібно, див. Знімок екрана:

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

1. ЗНАЙТИ (".", 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 символів, які починаються з четвертого символу з комірки А2.


Розділіть третій октет IP-адрес:

Щоб витягти третій октет IP-адрес, функції MID і FIND також можуть зробити вам послугу, будь ласка, введіть або скопіюйте наступну формулу в порожню комірку:

=MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1))

Потім перетягніть маркер заповнення вниз, щоб отримати необхідні результати, і лише третій октет 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-адреси, скопіюйте або введіть формулу в порожню комірку:

=MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))

А потім перетягніть маркер заповнення, щоб заповнити цю формулу в інші потрібні комірки, ви отримаєте результат, як показано на знімку екрана нижче:

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

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: Загальна довжина комірки А2 віднімає позицію третьої крапки, щоб отримати число решти символів. Це отримає число 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.


Використовувані відносні функції:

  • LEFT:
  • Функція LEFT витягує задану кількість символів з лівого боку поданого рядка.
  • MID:
  • Функція MID використовується для пошуку та повернення певної кількості символів із середини даного текстового рядка.
  • FIND:
  • Функція FIND використовується для пошуку рядка в іншому рядку та повертає початкове положення рядка всередині іншого.
  • LEN:
  • Функція LEN повертає кількість символів у текстовому рядку.

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

  • Розділити текст і цифри в комірці в Excel
  • Якщо припустити, що дані комірки змішуються з текстом і цифрами, як можна розділити їх на окремі комірки стовпців? Цей підручник покаже вам докладні кроки, щоб отримати це за допомогою формул.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (1)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
This worked beautifully, thank you for this information!!
Rated 5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations