Як розділити клітинки в Excel (розділити на кілька стовпців)

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

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

Або ви отримуєте адресу ", і вам потрібно розділити адресу, щоб ви могли аналізувати міста або пін -код окремо.

Як розділити комірки в Excel

У цьому посібнику ви дізнаєтесь, як розділити клітини в Excel за допомогою таких методів:

• Використання функції "Текст у стовпці".
• Використання текстових функцій Excel.
• Використання Flash Fill (доступно у 2013 та 2016 роках).

Давайте почнемо!

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

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

Ось кроки, щоб розділити ці імена на ім’я та прізвище:

• Виберіть клітинки, у яких у вас є текст, який потрібно розділити (у цьому випадку A2: A7).
• Натисніть на вкладку Дані
• У групі "Інструменти даних" натисніть "Текст у стовпці".
• У майстрі перетворення тексту в стовпці:
  • Крок 1 з 3 майстра перетворення тексту до стовпців: Переконайтеся, що вибрано розділений (це вибір за умовчанням). Це дозволить вам відокремити ім’я та прізвище на основі зазначеного роздільника (у цьому випадку пробіл).
  • Натисніть Далі.
  • Крок 2 з 3 Майстер надсилання тексту до стовпців: Виберіть пробіл як роздільник і зніміть позначку з всього іншого. Ви можете побачити, як виглядатиме ваш результат у розділі Попередній перегляд даних діалогового вікна.
  • Натисніть Далі.
  • Крок 3 з 3 Майстер надсилання текстів до стовпців: На цьому кроці ви можете вказати формат даних та місце, де потрібно отримати результат. Я буду зберігати формат даних як загальний, оскільки маю текстові дані для поділу. Місце призначення за замовчуванням - A2, і якщо ви продовжите це, він замінить вихідний набір даних. Якщо ви хочете зберегти вихідні дані недоторканими, виберіть іншу клітинку як пункт призначення. У цьому випадку вибирається B2.
  • Натисніть кнопку Готово.

Це миттєво розділить текст комірки на дві різні колонки.

Примітка:

• Функція "Текст у стовпець" розбиває вміст комірок на основі роздільника. Хоча це добре працює, якщо ви хочете розділити ім’я та прізвище, у випадку імені, по батькові та прізвища воно розділить його на три частини.
• Результат, який ви отримуєте від використання функції «Текст у стовпець», є статичним. Це означає, що якщо відбуваються будь -які зміни у вихідних даних, вам доведеться повторити процес, щоб отримати оновлені результати.

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

Функції Excel Text чудово підходять, коли потрібно нарізати і нарізати текстові рядки.

Хоча функція Text to Column дає статичний результат, результат, який ви отримуєте від використання функцій, є динамічним і автоматично оновлюється при зміні вихідних даних.

Розділення імен, які мають ім’я та прізвище

Припустимо, у вас є ті самі дані, що показано нижче:

Вилучення імені

Щоб отримати ім’я з цього списку, скористайтеся такою формулою:

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

Ця формула визначає перший символ пробілу, а потім повертає весь текст перед цим символом пробілу:

Ця формула використовує функцію SEARCH для визначення позиції пробілу. У випадку з Брюсом Уейном пробіл знаходиться на шостій позиції. Потім він видобуває всі символи ліворуч від нього за допомогою функції ВЛІВО.

Вилучення прізвища

Аналогічно, щоб отримати прізвище, скористайтеся такою формулою:

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

Ця формула використовує функцію пошуку для пошуку положення пробілу за допомогою функції ПОШУК. Потім він віднімає це число від загальної довжини імені (заданого функцією LEN). Це дає кількість символів у прізвищі.

Потім це прізвище витягується за допомогою функції ПРАВО.

Примітка: Ці функції можуть не працювати належним чином, якщо у вас є провідні, кінцеві або подвійні пробіли в іменах. Натисніть тут, щоб дізнатися, як видалити початкові/кінцеві/подвійні пробіли в Excel.

Розділення імен, які мають ім’я, по батькові та прізвище

Можуть бути випадки, коли ви отримуєте комбінацію імен, де деякі імена також мають по батькові.

Формула в таких випадках дещо складна.

Вилучення імені

Щоб отримати ім'я:

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

Це та сама формула, яку ми використовували, коли не було по батькові. Він просто шукає перший символ пробілу і повертає всі символи перед пробілом.

Вилучення по батькові

Щоб отримати по батькові:

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

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

Якщо середнього імені немає, функція MID повертає помилку. Щоб уникнути помилки, вона обгортається функцією IFERROR.

Вилучення прізвища

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

= IF (LEN (A2) -LEN (ЗАМІНА (A2, "", "")) = 1, ПРАВО (A2, LEN (A2) -ПОШУК ("", A2)), ПРАВО (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))

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

Але якщо їх 2, то він виділяє другий пробіл і повертає кількість символів після другого пробілу.

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

Розділити клітинки в Excel за допомогою Flash Fill

Flash Fill - це нова функція, представлена ​​в Excel 2013.

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

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

Флеш -заповнення працює, визначаючи шаблони та тиражуючи їх для всіх інших клітинок.

Ось як можна витягнути ім’я зі списку за допомогою Flash Fill:

• У клітинці В2 введіть ім’я Брюса Уейна (тобто Брюса).
• Вибравши клітинку, ви помітите маленький квадрат у правому кінці виділення комірки. Двічі клацніть по ньому. Це заповнить однакове ім’я у всіх клітинках.
• Коли клітинки будуть заповнені, унизу праворуч ви побачите значок «Параметри автозаповнення». Натисніть на неї.
• Виберіть зі списку Flash Fill.
• Щойно ви виберете Flash Fill, ви помітите, що всі осередки оновлюються, і тепер для кожного імені відображається ім’я.

Як працює Flash Fill?

Flash Fill шукає шаблони в наборі даних і повторює шаблон.

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

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

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

Чесно кажучи, це все ще хороше наближення цієї тенденції. Однак це не те, що я хотів.

Але це все ще досить хороший інструмент, який можна тримати у своєму арсеналі та використовувати, коли виникає така необхідність.

Ось ще один приклад, коли Flash Fill працює блискуче.

У мене є набір адрес, з яких я хочу швидко витягти місто.

Щоб швидко отримати місто, введіть назву міста для першої адреси (введіть Лондон у клітинку В2 у цьому прикладі) та скористайтесь функцією автозаповнення, щоб заповнити всі осередки. Тепер використовуйте Flash Fill і миттєво дасть вам назву міста з кожної адреси.

Так само ви можете розділити адресу та витягнути будь -яку її частину.

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

Якщо ви спробуєте використати Flash Fill, коли шаблону немає, він покаже вам помилку, як показано нижче:

У цьому підручнику я розглянув три різні способи поділу клітинок у Excel на кілька стовпців (за допомогою тексту в стовпці, формул та заповнення Flash)

Сподіваюся, цей підручник з Excel був вам корисним.