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

Досліджуйте нове, Безкоштовна версія Kutools для Outlook сьогодні!

 

Витягніть імена по батькові та прізвища з повного імені в Excel

Автор: Сяоян Остання зміна: 2020-11-06

Припустимо, у вас є список імен користувачів, тепер ви хочете розділити повні імена на імена, імена та прізвища в окремих стовпцях, як показано на наступному знімку екрана. У цій статті я представив деякі формули роботи з цією роботою в Excel.

Витягніть або отримайте імена з повних імен у Excel

Щоб отримати або отримати перші імена з повних імен, функція LEFT та SEARCH може допомогти вам, загальний синтаксис:

=LEFT(name,FIND(" ",name)-1)
  • name: Повне ім'я або посилання на клітинку, з яких потрібно витягти спочатку.

Скопіюйте або введіть наступну формулу в порожню клітинку, де ви хочете отримати результат:

=LEFT(A2,SEARCH(" ",A2)-1)

Потім перетягніть маркер заповнення до клітинок, до яких потрібно застосувати цю формулу, і всі імена будуть витягнуті, як показано на знімку екрана нижче:

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

ПОШУК ("", A2) -1: Ця функція SEARCH використовується для отримання позиції першого пробілу, віднімання 1 означає, що кількість символів, яку ви хочете, на один менше, ніж позиція пробілу. Це дасть результат: 5.

ВЛІВО (A2, ПОШУК ("", A2) -1) = ВЛІВО ("Jerry T. Stone", 5): Ця функція LEFT витягує текст із повним ім'ям "Jerry T. Stone" з лівого боку з 5 символами, які повертає функція SEARCH.

Витягніть або отримайте прізвища з повних імен у Excel

Щоб вилучити прізвища з повних імен, комбінація функцій ПРАВО, ЛІНЬ, ЗНАЙТИ, ЛІНЯ та ЗАМІНА може зробити вам послугу. Загальний синтаксис:

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
  • name: Повне ім’я або посилання на клітинку, з яких потрібно витягти прізвище.

Скопіюйте або введіть формулу нижче в порожню комірку, де ви хочете отримати прізвища:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

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

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

1. LEN (A2) -LEN (ЗАМІСТ (A2, "", "")): Ця частина використовується для отримання кількості пробілів у клітинці A2.

  • LEN (A2): Ця функція LEN повертає загальну кількість символів у комірці A2. Повернеться: 14.
  • ЗАМІННИК (A2, "", ""): Ця функція ЗАМІНИ використовується для заміни всіх пробільних символів нічим. І ви отримаєте результат таким: «ДжеріТ.Камінь".
  • LEN (ЗАМІСТНИК (A2, "", ""): Отримайте загальну довжину повного імені в комірці A2 без пробілів.
  • LEN (A2) -LEN (ЗАМІСТ (A2, "-", "")): Відніміть довжину повного імені без пробілу від загальної довжини повного імені, щоб отримати номер пробілу, і це отримає 2.

2. ЗАМІНИТИ (A2, "", "*", LEN (A2) -LEN (ЗАМІНИТИ (A2, "", ""))) = ЗАМІНИТИ (A2, "", "*", 2): Ця функція ЗАМІНИ використовується для заміни останнього входження простору, який повертається формулою першої частини символом *. І ви отримаєте такий результат: “Джеррі Т. * Стоун".

3. FIND ("*", ЗАМІСТ (A2, "", "*", LEN (A2) -LEN (ЗАМІСТ (A2, "", "")))) = FIND ("*", "Jerry T. *Камінь" ): Функція FIND поверне позицію символу * у текстовому рядку, який повертає функція SUBSTITUTE, і ви отримаєте число 9.

4. LEN (A2) -FIND ("*", ЗАМІСТ (A2, "", "*", LEN (A2) -LEN (ЗАМІСТ (A2, "", "")))) = LEN (A2) - 9: Ця частина формули визначає, скільки символів є після останнього пробілу. Це отримає число 5. Це буде розпізнано як аргумент num_chars у функції ВПРАВО.

5. ПРАВО (A2, LEN (A2) -FIND ("*", ЗАМІСТ (A2, "", "*", LEN (A2) -LEN (ЗАМІСТ (A2, "", "")))))) = ПРАВО (A2, 5): Нарешті, функція ВПРАВО використовується для вилучення 5 символів, які повертаються формулою на кроці 4 з правого боку текстового рядка в комірці А2.

примітки: Якщо ваш список повних імен містить лише ім’я та прізвище, ви можете застосувати таку просту формулу, щоб отримати прізвище, як вам потрібно:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

Витягніть або отримайте по батькові з повних імен у Excel

Якщо вам потрібно вилучити середні імена з повних імен, ця формула, яка створюється функціями MID та SEARCH. Загальний синтаксис:

=MID(name, SEARCH(" ", name) + 1, SEARCH(" ", name, SEARCH(" ", name)+1) - SEARCH(" ", name)-1)
  • name: Повне ім’я або посилання на клітинку, з яких потрібно витягти друге ім’я.

Будь ласка, введіть або скопіюйте наступну формулу в порожню комірку, щоб вивести результат:

=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)

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

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

Для вилучення середнього імені спочатку слід знайти положення обох пробілів між повним іменем, а потім застосувати функцію MID для вилучення тексту в середині текстового рядка.

1. ПОШУК ("", A2) + 1: Ця функція ПОШУК отримає позицію першого пробілу, додавши 1 засіб для початку вилучення з наступного символу. Це отримає число 7. Ця частина розпізнається як аргумент start_num у функції MID.

2. ПОШУК ("", A2, ПОШУК ("", A2) +1) - ПОШУК ("", A2) -1: Ця частина розпізнається як аргумент num_chars у функції MID.

  • ПОШУК ("", A2, ПОШУК ("", A2) +1): Ця частина формули використовується для отримання положення другого пробілу, вона отримає число 9.
  • ПОШУК ("", A2): Ця функція ПОШУК отримає позицію першого пробілу в комірці А2. Він отримає число 6.
  • ПОШУК ("", А2, ПОШУК ("", А2) +1) - ПОШУК ("", А2) -1 = 9-6-1: Відніміть положення першого пробілу від положення другого пробілу, а потім відніміть 1 від результату означає видалити провідний пробіл. І результат 2.

3. MID (A2, SEARCH ("", A2) + 1, SEARCH ("", A2, SEARCH ("", A2) +1) - SEARCH ("", A2) -1) = MID (A2,7,2 , XNUMX): Нарешті, функція MID витягне 2 символи в середині комірки A2, яка починається з сьомого символу.

примітки: Якщо в повних іменах є лише імена та прізвища, наведена вище формула отримає значення помилки, як показано нижче:

Щоб виправити цю проблему, слід додати наведену формулу до функції IFERROR, застосуйте таку формулу:

=IFERROR(MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1),"")

Вийміть або отримайте імена, прізвища та прізвища з повних імен з дивовижною особливістю

Якщо вам боляче з вищезазначеним фромулом, тут я порекомендую простий інструмент - Kutools для Excel, З його Розділені імена Ви можете виділити всі імена, проміжні та прізвища відразу в окремі комірки.  Натисніть, щоб завантажити Kutools для Excel для безкоштовної пробної версії!

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

  • LEFT:
  • Функція LEFT витягує задану кількість символів з лівого боку поданого рядка.
  • RIGHT:
  • Функція ВПРАВО використовується для виділення певної кількості символів з правої сторони текстового рядка
  • MID:
  • Функція MID використовується для пошуку та повернення певної кількості символів із середини даного текстового рядка.
  • LEN:
  • Функція LEN повертає кількість символів у текстовому рядку.
  • FIND:
  • Функція FIND використовується для пошуку рядка в іншому рядку та повертає початкове положення рядка всередині іншого.
  • SEARCH:
  • Функція ПОШУК може допомогти вам знайти позицію певного символу або підрядка з даного текстового рядка
  • SUBSTITUTE:
  • Функція Excel ЗАМІНИ замінює текст або символи в текстовому рядку іншим текстом або символами.

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

  • Витягніть ім’я першого і прізвища із повного імені
  • Припустимо, у вас є список повних імен, тепер ви хочете витягти перше початкове та прізвище або ім'я та перший прізвище з повного імені, як показано на скріншотах нижче. Як ви могли вирішувати ці завдання в книзі Excel?
  • Перевернути або змінити імена та прізвища у списку Excel
  • Якщо у вас є список повних імен, які відформатовані як прізвище та ім'я, зараз ви хочете перегорнути прізвище та ім'я на ім'я та прізвище, як показано на знімку екрана нижче. У цій статті я представив деякі формули роботи з цією роботою в Excel.
  • Витягніть кілька клітинок із клітини
  • Якщо у вас є список текстових рядків, які розділені розривами рядків (це відбувається натисканням клавіш Alt + Enter при введенні тексту), і тепер ви хочете витягти ці рядки тексту в кілька комірок, як показано на знімку екрана нижче. Як ви могли це вирішити за допомогою формули в Excel?
  • Витяг N-го слова з текстового рядка в Excel
  • Якщо у вас є список текстових рядків або речень, то зараз ви хочете витягти конкретне n-те слово зі списку, як показано нижче. У цій статті я ознайомлю з деякими методами вирішення цієї роботи в Excel.

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

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

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер 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.