У Excel дуже легко розділити ім’я, по батькові та прізвище, які з’являються в одному стовпці, на окремі стовпці різними способами.
Припустимо, ви отримали список контактів з усіма іменами, зазначеними в повних іменах, в одному стовпці, і вам, ймовірно, потрібно буде розділити ім’я, по батькові та прізвище та розділити їх на окремі стовпці. Існує кілька різних способів розділення імен – за допомогою функції «Текст у стовпці», Flash-заливки та формул.
У Excel дуже легко розділити імена з одного стовпця на два або більше стовпців. У цій статті ми покажемо вам, як розділити імена на різні стовпці в Excel за допомогою різних способів.
Як розділити імена в Excel
Є три простих способи розділити імена в Excel. Залежно від структури даних і того, чи хочете ви, щоб імена розділів були статичними чи динамічними, виберіть один із наступних методів:
- Розділіть імена за допомогою функції «Текст у стовпці».
- Розділіть імена за допомогою формул
- Розділіть імена за допомогою Flash Fill
Розділіть імена за допомогою майстра тексту в стовпці
Майстер тексту в стовпці є одним із найпоширеніших способів розділення імені та прізвища в Excel, а також по батькові. Переконайтеся, що поруч із іменами, які ви збираєтеся розділити, є порожні стовпці, оскільки повні імена будуть розділені на окремі стовпці.
Наприклад, у вас є наведений нижче набір даних з повними іменами, і ви хочете розділити/розділити ім’я та прізвище та зберегти їх в окремих клітинках.
Спочатку виділіть стовпець з повними іменами, які потрібно відокремити. Потім перейдіть на вкладку «Дані» та натисніть опцію «Текст у стовпці» в розділі «Інструменти для даних».
Відкриється майстер перетворення тексту в стовпці. На першому кроці майстра виберіть опцію «Розділені» та натисніть «Далі».
На кроці 2 з 3 майстра перетворення тексту в стовпці виберіть роздільник, який розділяє ваші дані, зніміть усі інші галочки та натисніть «Далі». У нашому випадку «пробіл» розділяє ім’я та прізвище, тому ми вибираємо цей роздільник.
Розділ попереднього перегляду даних нижче у вікні показує, як аналізуються ваші імена.
На кроці 3 з 3 ви вибираєте формат даних і місце призначення та натискаєте «Готово».
Зазвичай для більшості типів даних стандартний «Загальний» буде працювати нормально. У полі «Призначення» вкажіть місце, де потрібно відображати вихідні дані. Ви повинні вказати адресу першої комірки в стовпці, де ви хочете отримати результати (В2, у нашому випадку).
Пам’ятайте, що якщо ви не вкажете цільову клітинку, майстер перезапише вихідні дані, тому переконайтеся, що ви вибрали порожній стовпець.
Після натискання кнопки «Готово» повні імена миттєво розділяться на дві окремі колонки (ім’я та прізвище).
Виконайте ті самі дії, якщо у вас є ім’я, по батькові та прізвище, і ваші імена будуть розділені на три стовпці замість двох.
Примітка: Результат цього методу статичний. Це означає, що якщо ви зміните оригінальну назву, вам потрібно зробити це знову, щоб розділити імена.
Розділені назви, розділені комами
Якщо ім’я та прізвище розділені комами, виконайте наведені нижче дії, щоб видалити кому та розділити ім’я та прізвище.
У наведеному нижче прикладі імена відформатовано у зворотному форматі (прізвище, ім’я), де спочатку ставиться прізвище, потім кома, а потім ім’я.
Виберіть назви та перейдіть до «Дані» –> «Текст у стовпець». На кроці 1 виберіть «Розділитель» і натисніть «Далі». На кроці 2 у розділі «Розмежувачі» поставте прапорець «Кома» (,) як роздільник, оскільки ваші імена розділяються комою.
На останньому кроці ви вибираєте формат даних як «Загальний», вказуєте місце призначення та натискаєте «Готово».
Тепер у вас будуть імена в окремих стовпцях.
Розділіть назви за допомогою формул
Майстер тексту в стовпці дозволяє швидко й легко розділяти імена. Однак, якщо ви хочете редагувати оригінальні назви і хочете динамічний метод, який автоматично оновлюватиметься щоразу, коли ви змінюєте імена, то розділення імен за допомогою формул є правильним вибором. Для розділення імен можна використовувати функції ЛІВО, ВПРАВО, СЕРЕДНИЙ, ЛЕН і ПОШУК або ПОШУК.
Розділіть ім’я та прізвище в Excel за допомогою формул
Отримати ім’я
Скажімо, у вас є наведений нижче набір даних, і ви хочете виділити ім’я в окрему клітинку. Щоб отримати ім’я, потрібно об’єднати функції FIND і LEFT в одну формулу.
Використовуйте таку формулу, щоб отримати ім’я:
=ВЛІВО(A2,ЗНАЙТИ(" ",A2)-1)Ця формула використовує функцію FIND, щоб знайти позицію пробілу (“ “) між іменем та прізвищем, і віднімає 1, щоб виключити сам пробіл. Потім це число передається функції ЛІВО, яка використовує цей номер позиції для вилучення всього тексту перед ним. Ви також можете використовувати функцію SEARCH замість функції FIND.
Після того, як ви введете формулу в порожню клітинку (B2), перетягніть маркер заповнення до інших клітинок, щоб застосувати цю формулу, і всі імена будуть розділені на стовпець B, як показано нижче:
Ви можете або вкласти функції ПОШУК і ЗНАЙТИ всередині функції ЛІВО, щоб витягти ім’я. Основна відмінність між цими двома функціями полягає в тому, що FIND чутливий до регістру, тоді як SEARCH не чутливий до регістру.
Якщо у вас є кома (,) замість пробілу між іменем та прізвищем, то використовуйте кому як перший аргумент у функції FIND:
=ВЛІВО(A2,ЗНАЙТИ(",",A2)-1)Отримати прізвище
Тепер, якщо вам потрібно витягти прізвище, скористайтеся функцією ПРАВО. Наступна формула витягне прізвище з того самого набору даних:
=ВПРАВО(A2,LEN(A2)-ЗНАЙТИ(" ",A2))Формула спочатку знаходить позицію пробілу, віднімає це число від загальної довжини рядка (яку повертає функція LEN), а потім це число передається функції ПРАВ, щоб витягти стільки символів з кінця рядка. (ім'я).
Розділіть ім’я, по батькові та прізвище в Excel за допомогою формул
Для розділення імен, які містять по батькові, потрібні різні формули, залежно від формату імені.
Щоб отримати ім'я якщо у вас є по батькові або ініціал по батькові, використовуйте ту саму формулу ЗНАЙТИ ЛІВО, з якою ви вже знайомі.
Отримати прізвище
Наведена вище формула ПРАВОГО ЗНАЙКУ добре працює, коли є лише ім’я та прізвище, вона не буде особливою користю, якщо ваші оригінальні імена містять по батькові або по батькові. Це тому, що ви не врахували два пробіли в назві.
Щоб отримати прізвище, якщо у вас також є по батькові, використовуйте цю формулу:
=ВПРАВО(A2,LEN(A2)-ПОШУК(" ",A2,ПОШУК(" ",A2,1)+1))Щоб витягти прізвище, спочатку визначте позицію другого символу пробілу за допомогою вкладених функцій SEARCH, додайте 1 до SEARCH(” “,A2,1), щоб почати вилучення з наступного символу. Далі від загальної довжини рядка відніміть позицію 2-го пробілу та отримайте довжину прізвища як число результату. Потім передайте отримане число функції ПРАВ, щоб отримати кількість символів з кінця рядка.
Отримайте по батькові
Функція MID використовує три аргументи, перший аргумент вказує текст або адресу комірки, другий вказує початкову позицію, а останній аргумент вказує кількість символів, щоб отримати друге ім’я з цієї позиції.
Синтаксис:
=MID(текст, початкова_число, кількість_символів)Щоб отримати по батькові, введіть цю формулу в порожню клітинку:
=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)Давайте подивимося, як працює ця складна формула:
Щоб витягти по батькові або середнє ім’я, потрібно визначити положення обох пробілів у повному імені. Щоб знайти позицію першого пробілу, введіть це ПОШУК(" ", A2) функцію в аргументі «початковий_номер» і додайте 1, щоб почати вилучення з наступного символу.
Потім, щоб дізнатися довжину по батькові, поставте це ПОШУК(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1 вкладена функція в аргументі ‘num_chars’, яка віднімає позицію 1-го пробілу від позиції 2-го пробілу та віднімає 1 з результату, щоб видалити пробіл в кінці. Кінцевий результат покаже вам, скільки символів потрібно витягти.
Тепер функція MID із початковою позицією по батькові та цифрами для вилучення відокремлює по батькові від повного імені (A2).
Розділіть імена в Excel за допомогою Flash Fill
Flash-заливка в основному використовується для автоматичного заповнення даних певного шаблону. Його також можна використовувати для розділення імен. Він доступний лише в Excel 2013, 2016, 2019 і 365.
Скажімо, у вас є наведений нижче набір даних, і ви хочете отримати лише імена з повних імен.
Введіть ім’я в клітинку, що розташована поруч із вихідним ім’ям. У цьому випадку введіть «Steve» в клітинку B2.
Потім почніть вводити ім’я в другій клітинці стовпця. Якщо під час введення тексту Excel розпізнає шаблон, Flash Fill автоматично покаже вам список імені в інших клітинках (сірим кольором).
Коли ви побачите список імен сірим кольором і якщо ці імена правильні, просто натисніть клавішу «Enter», і Flash Fill автоматично заповнить решту стовпця іменами.
Виконайте ту ж процедуру, щоб розділити прізвища в окрему колонку.
Результат:
Flash Fill працює, виявляючи шаблон у цих даних і слідуючи цьому шаблону, надаючи вам змінені дані. Спочатку, коли ви вводите ім’я в першу клітинку, Flash Fill не розпізнає шаблон. Але коли ви знову почнете вводити ім’я в другій клітинці, Flash Fill розпізнає шаблон і покаже вам пропозицію щодо розділення імен. Потім просто натисніть клавішу «Enter».
Як правило, функція Flash Fill увімкнена за замовчуванням. Якщо це не працює у вашому Excel, після введення імені в першу клітинку ви можете просто вибрати другу клітинку та натиснути кнопку «Flash Fill» у групі «Інструменти даних» на вкладці «Дані».
Крім того, ви також можете натиснути «Ctrl» + «E», щоб отримати ті ж результати.
Тепер інші клітинки будуть заповнені іменами.
Іноді ви можете не бачити пропозицію візерунка сірим кольором, у цьому випадку ви можете використовувати маркер заповнення, щоб отримати результат Flash Fill.
Спочатку введіть імена вручну в дві клітинки та виберіть обидві ці клітинки. Потім наведіть курсор на нижній правий кут виділення. Ви помітите, що курсор змінюється з маленького зеленого квадрата (значок заповнювача) на значок плюса.
Далі двічі клацніть на значку плюса. Це заповнить решту осередків. На цьому етапі результати невірні, ви побачите, що обидва імена повторюються знову і знову. Потім у нижньому правому куті отриманих даних ви побачите невеликий значок автозаповнення, як показано нижче. Натисніть на цю іконку «Автозаповнення» та виберіть «Заливка Flash».
Це заповнить імена в усіх клітинках:
Видалити по батькові
Ви також можете скористатися інструментом Flash Fill, щоб позбутися по батькові від повного імені.
Наприклад, припустимо, що у вас є наведений нижче набір даних, і ви хочете отримати лише ім’я та прізвище без по батькові чи по батькові.
Щоб отримати імена без по батькові або середніх ініціалів, вручну введіть «Лорд Старк» у сусідній клітинці. Потім у другій сусідній клітинці введіть «Дейенерис Таргарієн». Поки ви вводите текст, Flash Fill розпізнає шаблон і покаже вам список імен без по батькові (сірим).
Якщо пропозиція правильна, натисніть клавішу «Enter», і Flash Fill автоматично заповнить решту клітинок іменами без по батькові.
Якщо ви хочете отримати лише по батькові без імен і прізвищ, введіть по батькові в перші дві клітинки та скористайтеся інструментом Flash Fill, щоб отримати по батькові з усіх повних імен у стовпці.
У цьому посібнику показано, як розділяти імена під час маніпулювання текстовими даними. Ці методи також можуть бути корисними при роботі з іншими формами даних, такими як адреси, назви продуктів, назви брендів тощо.