Note: The other languages of the website are Google-translated. Back to English

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

Припустимо, у вас є список імен користувачів, тепер ви хочете розділити повні імена на імена, імена та прізвища в окремих стовпцях, як показано на наступному знімку екрана. У цій статті я представив деякі формули роботи з цією роботою в 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 для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (5)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
ти чемпіон! дуже корисний!
Цей коментар був мінімізований модератором на сайті
Eu gostaria de ter uma list de todos nomes exceptiono o ultimo. Portanto, uma lista de nome completo sem o sobrenome.
Цей коментар був мінімізований модератором на сайті
Eu gostaria de extrair o primeiro e do meio nomes juntos.
Цей коментар був мінімізований модератором на сайті
عندى اسم خماسي وعاوز لغايه الاسم الرباعى فقط
Цей коментар був мінімізований модератором на сайті
привіт,
Спробуйте наведену нижче формулу:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (4-1)*LEN(A2)+1, LEN(A2)))
Сподіваюся, це допоможе вам!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL