Розділіть ім’я та прізвище в Excel (розділити імена за допомогою формул)

Перегляд відео - Розділіть імена в Excel (на ім’я, по батькові та прізвище)

Excel - це чудовий інструмент для нарізки та нарізки текстових даних.

Існує так багато корисних формул, а також функціональних можливостей, які можна використовувати для роботи з текстовими даними в Excel.

Одне з найпоширеніших запитань щодо маніпулювання текстовими даними - "Як розділити ім’я та прізвище (або ім’я, по батькові та прізвища) в Excel?“.

Є кілька простих способів розділити імена в Excel. Вибраний вами метод буде залежати від того, як структуровані ваші дані, і від того, чи хочете ви, щоб результат був статичним або динамічним.

Тож давайте почнемо і розглянемо різні способи поділу імен у Excel.

Розділити імена за допомогою тексту до стовпців

Функціонал Text to Columns в Excel дозволяє швидко розбивати текстові значення на окремі клітинки підряд.

Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете відокремити ім’я та прізвище та отримати їх в окремих осередках.

Нижче наведено кроки для відокремлення імені та прізвища за допомогою тексту в стовпці:

1. Виберіть усі імена у стовпці (A2: A10 у цьому прикладі)
2. Перейдіть на вкладку «Дані»
3. У групі "Інструменти даних" натисніть опцію "Текст у стовпці".
4. Внесіть у майстер перетворення тексту в стовпець такі зміни:
   1. Крок 1 з 3: Виберіть Розділений (це дозволяє використовувати пробіл як роздільник) і натисніть Далі
   2. Крок 2 з 3: Виберіть опцію Пробіл і натисніть Далі
   3. Крок 3 з 3: Встановіть B2 як осередок призначення (інакше він перезапише наявні дані)
5. Натисніть кнопку Готово

Вищезазначені кроки миттєво розділили б імена на ім’я та прізвище (з іменами у стовпці В та прізвищем у стовпці С).

Примітка: Якщо у клітинках вже є дані (ті, де очікується виведення тексту в стовпці), Excel покаже вам попередження, яке дасть вам знати, що деякі дані вже є у клітинках. Ви можете вибрати, щоб перезаписати дані або скасувати текст у стовпці, і спочатку видалити його вручну.

Після цього ви можете видалити дані повного імені, якщо хочете.

Кілька речей, які потрібно знати при використанні тексту в стовпцях для розділення імені та прізвища в Excel:

1. Результат цього статичний. Це означає, що у випадку, якщо у вас є нові дані або вихідні дані мають деякі зміни, вам потрібно повторити це знову, щоб розділити імена.
2. Якщо вам потрібно лише ім’я або лише прізвище, ви можете пропустити стовпці, які вам не потрібні, на кроці 3 діалогового вікна «Майстер перетворення тексту в стовпець». Для цього виберіть стовпець у попередньому перегляді, який потрібно пропустити, а потім виберіть опцію «Не імпортувати стовпець (пропустити)».
3. Якщо ви не вказуєте осередок призначення, «Текст у стовпець» перепише поточний стовпець

Опція «Текст у стовпці» найкраще підходить, якщо у вас є послідовні дані (наприклад, усі імена мають лише ім’я та прізвище, або всі імена мають ім’я, по батькові та прізвище).

У цьому прикладі я показав вам, як відокремити імена, які мають пробіл як роздільник. Якщо роздільник - це кома або комбінація коми і пробілу, ви все одно можете використовувати ті самі кроки. У цьому випадку ви можете вказати роздільник на кроці 2 з 3 майстра. Вже є можливість використовувати кому як роздільник, або ви можете вибрати опцію "Інше" та вказати спеціальний роздільник.

Хоча текст у стовпці - це швидкий та ефективний спосіб поділу імен, він підходить лише тоді, коли ви хочете, щоб результат був статичним результатом. Якщо у вас є набір даних, який може розширюватися або змінюватися, вам краще скористатися формулами для розділення імен.

Розділіть ім’я, ім’я та по батькові за допомогою формул

Формули дозволяють нарізати та нарізати текстові дані та витягти те, що вам потрібно.

У цьому розділі я поділюся різними формулами, які можна використовувати для розділення даних імен (на основі того, як структуровані ваші дані).

Нижче наведені три формули, за допомогою яких можна розділити ім’я, по батькові та прізвище (детально пояснено далі в наступних розділах).

Формула отримання імені:

= ВЛІВО (A2, ПОШУК ("", A2) -1)

Формула для отримання по батькові:

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

Формула отримання прізвища:

= ПРАВО (A15, LEN (A15) -ПОШУК ("@", ЗАМІНА (A15, "", "@", LEN (A15) -ЛЕН (ЗАМІНА (A15, "", "")))))

Отримайте ім'я

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

Формула нижче дасть вам ім'я:

= ВЛІВО (A2, ПОШУК ("", A2) -1)

У наведеній вище формулі використовується функція SEARCH для визначення позиції символу пробілу між іменем та прізвищем. Потім функція LEFT використовує цей номер пробілу для вилучення всього тексту перед ним.

Це досить пряме використання вилучення частини текстового значення. Оскільки все, що нам потрібно зробити, це визначити першу позицію символу пробілу, не має значення, чи ім’я має якесь по батькові чи ні. Наведена вище формула буде працювати нормально.

Тепер давайте трохи вдосконалимося з кожним прикладом.

Отримайте прізвище

Припустимо, у вас однаковий набір даних, і цього разу вам потрібно отримати прізвище.

Наведена нижче формула витягне прізвище з наведеного вище набору даних:

= ПРАВО (A2, LEN (A2) -ПОШУК ("", A2))

Знову ж таки, досить просто.

Цього разу ми спочатку знаходимо позицію символу пробілу, яка потім використовується для з’ясування кількості символів, що залишилися після пробілу (це буде прізвище).

Це досягається шляхом віднімання позиційного значення пробілу із загальною кількістю символів у назві.

Потім це число використовується в функції ПРАВО для отримання всіх цих символів праворуч від імені.

Хоча ця формула чудово працює, коли є лише ім’я та прізвище, вона не спрацює, якщо у вас також є по батькові. Це тому, що ми враховували лише один пробіл (між ім’ям та прізвищем). Наявність по батькові додає до імені більше символів пробілу.

Щоб отримати прізвище, якщо у вас також є по батькові, використовуйте формулу нижче:

= ПРАВО (A15, LEN (A15) -ПОШУК ("@", ЗАМІНА (A15, "", "@", LEN (A15) -ЛЕН (ЗАМІНА (A15, "", "")))))

Тепер це стало дещо складним … чи не так?

Дозвольте мені пояснити, як це працює.

Наведена вище формула спочатку знаходить загальну кількість символів пробілу в назві. Це робиться шляхом отримання довжини імені з пробілом та без нього, а потім віднімання того, що без пробілу, з того, що з пробілом. Це дає загальну кількість символів пробілу.

Потім функція SUBSTITUTE використовується для заміни останнього символу пробілу символом «@» (ви можете використовувати будь -який символ - те, що навряд чи буде зустрічатися як частина імені).

Після заміни символу @ замість останнього символу пробілу можна легко знайти позицію цього символу @. Це робиться за допомогою функції ПОШУК.

Тепер все, що вам потрібно зробити, це витягнути всі символи праворуч від цього символу @. Це робиться за допомогою функції ПРАВО.

Отримайте по батькові

Припустимо, у вас є набір даних, як показано нижче, і ви хочете витягнути ім'я по батькові.

Наступна формула зробить це:

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

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

Початкове положення легко знайти за допомогою функції ПОШУК.

Найскладніше - знайти, скільки символів потрібно видобути після цієї вихідної позиції. Щоб отримати це, вам потрібно визначити, скільки символів є від початкової позиції до останнього символу пробілу.

Це можна зробити за допомогою функції SUBSTITUTE і замінити останній пробіл символом «@». Як тільки це буде зроблено, ви можете легко скористатися функцією SEARCH, щоб знайти позицію цього останнього символу пробілу.

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

Однією з переваг використання формули для розділення імен є те, що результат є динамічним. Тож у разі розширення вашого набору даних і додавання до нього інших імен або якщо деякі імена змінюються у вихідному наборі даних, вам не потрібно турбуватися про отримані дані.

Окремі імена за допомогою функції «Знайти та замінити»

Мені подобається гнучкість, яка поставляється з "Знайти та замінити" - тому що ви можете використовувати символи підстановки.

Дозвольте мені спочатку пояснити, що таке персонаж підстановки.

Символ підстановки - це те, що можна використовувати замість будь -якого тексту. Наприклад, ви можете використовувати символ зірочки (*), і він буде представляти будь -яку кількість символів у Excel. Щоб навести вам приклад, якщо я хочу знайти всі імена, які починаються з алфавіту A, я можу використовувати A* у пошуку та заміні. Буде знайдено та виділено всі клітинки, де назва починається на А.

Якщо вам все ще неясно, не хвилюйтесь. Продовжуйте читати, і з наступних кількох прикладів буде зрозуміло, що таке символи підстановки та як використовувати їх для швидкого розділення імен (або будь -яких текстових значень у Excel).

У всіх наведених нижче прикладах обов’язково створіть резервну копію набору даних. Функція "Знайти та замінити" змінює дані, у яких вона використовується. Найкраще спочатку скопіювати та вставити дані, а потім скористатися функцією «Знайти та замінити» у скопійованому наборі даних.

Отримайте ім'я

Припустимо, у вас є набір даних, як показано нижче, і ви хочете отримати лише ім’я.

Нижче наведено кроки для цього.

1. Скопіюйте дані про імена у стовпці А та вставте їх у стовпець В.
2. Вибравши дані у стовпці В, перейдіть на вкладку Домашня сторінка
3. У групі Редагування натисніть Знайти та вибрати.
4. Натисніть Замінити. Відкриється діалогове вікно «Знайти та замінити».
5. У діалоговому вікні «Знайти та замінити» введіть наступне
   1. Знайдіть, що: * (пробіл після символу зірочки)
   2. Замінити на: залиште це поле порожнім
6. Натисніть Замінити все.

Наведені вище кроки дадуть вам ім’я та видалять все після імені.

Це працює, навіть якщо у вас є імена, які мають по батькові.

Професійна порада: Комбінація клавіш для відкриття діалогового вікна Знайти та замінити є Control + H (утримуйте клавішу керування, а потім натисніть клавішу H).

Отримайте прізвище

Припустимо, у вас є набір даних, як показано нижче, і ви хочете отримати лише прізвище.

Нижче наведено кроки для цього.

1. Скопіюйте дані про імена у стовпці А та вставте їх у стовпець В.
2. Вибравши дані у стовпці В, перейдіть на вкладку Домашня сторінка
3. У групі Редагування натисніть Знайти та вибрати.
4. Натисніть Замінити. Відкриється діалогове вікно «Знайти та замінити».
5. У діалоговому вікні «Знайти та замінити» введіть наступне
   1. Знайдіть що: * (символ зірочки після символу пробілу)
   2. Замінити на: залиште це поле порожнім
6. Натисніть Замінити все.

Наведені вище кроки дадуть вам прізвище та видалять все перед ім’ям.

Це працює, навіть якщо у вас є імена, які мають по батькові.

Видаліть по батькові

Якщо ви хочете позбутися лише імені по батькові та мати лише ім’я та прізвище, ви можете це зробити за допомогою функції «Знайти та замінити».

Припустимо, у вас є набір даних, як показано нижче, і ви хочете видалити ім’я по батькові з них.

Нижче наведено кроки для цього.

1. Скопіюйте дані про імена у стовпці А та вставте їх у стовпець В.
2. Вибравши дані у стовпці В, перейдіть на вкладку Домашня сторінка
3. У групі Редагування натисніть Знайти та вибрати.
4. Натисніть Замінити. Відкриється діалогове вікно «Знайти та замінити».
5. У діалоговому вікні «Знайти та замінити» введіть наступне
   1. Знайдіть, що: * (пробіл, після якого символ зірочки, а потім символ пробілу)
   2. Замінити на: (просто поставте тут пробіл)
6. Натисніть Замінити все.

Наведені вище кроки призведуть до видалення по батькові з повного імені. Якщо деякі імена не мають по батькові, вони не будуть змінені.

Окремі імена за допомогою Flash Fill

Заповнення флеш -пам'яттю було введено в Excel 2013 і полегшує зміну або очищення набору текстових даних.

А що стосується розділення даних про імена, то це прямо в провулку Flash Fill.

Найважливіше, що потрібно знати під час використання Flash Fill, - це наявність шаблону, який може ідентифікувати заповнення Flash. Після того, як він ідентифікує візерунок, він легко допоможе вам розділити імена в Excel (ви отримаєте більшу ясність у цьому, коли переглянете кілька прикладів нижче).

Отримайте ім’я або прізвище від повного імені

Припустимо, у вас є набір даних, як показано нижче, і ви хочете отримати лише ім’я.

1. У сусідній клітинці вручну введіть ім’я від повного імені. У цьому прикладі я б набрав Ріка.
2. У другій клітинці вручну введіть ім’я з назви сусідньої комірки. Під час введення тексту ви побачите, що Flash Fill автоматично показує список імені (сірим кольором).
3. Коли ви бачите імена сірим кольором, швидко перегляньте його, щоб переконатися, що він показує правильні імена. Якщо це правильно, натисніть клавішу Enter, і Flash Fill автоматично заповнить решту клітинок іменем.

Flash Fill потребує, щоб ви надали йому зразок, якого він може дотримуватися, надаючи змінені дані. У нашому прикладі, коли ви вводите ім’я у першій комірці, Flash Fill не може зрозуміти шаблон.

Але як тільки ви починаєте вводити ім’я у другій комірці, Flash Fill розуміє шаблон і показує вам деякі пропозиції. Якщо пропозиція правильна, просто натисніть клавішу Enter.

І якщо це неправильно, ви можете спробувати ввести вручну ще кілька клітинок і перевірити, чи Flash Fill здатний розпізнати шаблон чи ні.

Іноді ви можете не бачити візерунок сірим кольором (як показано на кроці 2 вище). Якщо це так, виконайте наведені нижче дії, щоб отримати результат Flash Fill:

1. Введіть текст вручну у дві клітинки.
2. Виділіть обидві ці клітинки
3. Наведіть курсор на праву нижню частину виділення. Ви помітите, що курсор зміниться на значок плюса
4. Двічі клацніть по ньому (ліва клавіша миші). Це заповнить усі клітинки. На даний момент результати, ймовірно, неправильні, і це не те, що ви очікували.
5. У нижньому правому куті отриманих даних ви побачите маленький значок автозаповнення. Натисніть на цю піктограму Автозаповнення
6. Натисніть на Flash Fill

Наведені вище кроки дадуть вам результат від Flash Fill (на основі шаблону, який він вивів).

Ви також можете використовувати Flash Fill, щоб отримати прізвище або по батькові. У перших двох клітинках введіть прізвище (або друге ім’я), і функція заповнення Flash зможе зрозуміти шаблон

Змінити назву за допомогою Flash Fill

Flash Fill - це розумний інструмент, який також може розшифрувати трохи складний малюнок

Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете змінити ім’я від Ріка Новака до Новака, Ріка (де прізвище йде спочатку, а потім - кома, а потім ім’я).

Нижче наведено кроки для цього.

1. У сусідній клітинці введіть вручну Новак, Рік
2. У другій клітинці введіть вручну Коннор, Сьюзен. Під час введення тексту ви побачите, як Flash Fill показує список імен у тому ж форматі (сірим).
3. Коли ви бачите імена сірим кольором, швидко перегляньте його, щоб переконатися, що він показує правильні імена. Якщо це правильно, натисніть клавішу Enter, і Flash Fill автоматично заповнить решту клітинок іменами в тому ж форматі.

Видаліть по батькові (або просто отримайте по батькові)

Ви також можете скористатися Flash Fill, щоб позбутися по батькові або отримати лише по батькові.

Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете отримати лише ім’я та прізвище, а не по батькові.

Нижче наведено кроки для цього.

1. У сусідній клітинці введіть вручну Рік Новак
2. У другій клітинці введіть вручну Сьюзен Коннор. Під час введення тексту ви побачите, як Flash Fill показує список імен у тому ж форматі (сірим).
3. Коли ви бачите імена сірим кольором, швидко перегляньте його, щоб переконатися, що він показує правильні імена. Якщо це правильно, натисніть клавішу Enter, і Flash Fill автоматично заповнить решту клітинок іменами без імені по батькові.

Аналогічно, якщо ви хочете отримати лише імена по батькові, введіть ім'я по батькові в перших двох осередках і використовуйте Flash Fill, щоб отримати ім'я по батькові з усіх інших імен.

У прикладах, показаних у цьому посібнику, використовуються імена під час маніпулювання текстовими даними. Ви можете використовувати ті ж концепції для роботи з іншими форматами даних (такими як адреси, назви продуктів тощо).