Як розділити клітинку на дві частини в Excel

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

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

Зміст

Розділіть комірки в Excel за допомогою опції "Перетворити текст у стовпці"

Нижче я маю список імен учнів школи:

Виділіть клітинки (у цьому випадку A2: A6).
Перейдіть до Дані -> Інструменти даних -> Перетворити.

У майстрі перетворення тексту в стовпці:

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

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

Примітка:

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

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

Текстові функції Excel чудово підходять для нарізання та розбиття рядків тексту. Хоча функція "Перетворити текст у стовпець" дає статичний результат, результат, отриманий за допомогою функцій, є динамічним та оновлюється автоматично при зміні вихідних даних.

Розбиття імен на ім’я та прізвище

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

Витяг власного імені

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

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

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

Ця формула використовує функцію SEARCH для отримання позиції пробілу. У випадку з "Sihame motawakil", пробіл знаходиться на 7-й позиції. Він витягує всі символи ліворуч від нього за допомогою функції ВЛІВО.

Витяг прізвища

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

= ВПРАВО (A2; NBCAR (A2) -ПОШУК ("; A2))

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

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

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

Розбиття імен на імена, імена та прізвища

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

Витяг власного імені

Щоб отримати перше ім'я: = ВЛІВО (A2; ПОШУК ("; A2) -1)

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

Витягніть прізвище

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

= IFERROR (STXT (A2; SEARCH ("; A2) +1; SEARCH ("; A2; SEARCH ("; A2) +1) -SEARCH ("; A2)); ")

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

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

Витяг прізвища

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

= IF (NBCAR (A2) -NBCAR (ЗАМІСТ (A2; "; "))) = 1; ВПРАВО (A2; NBCAR (A2) -ПОШУК ("; A2)); ВПРАВО (A2; NBCAR (A2) -ПОШУК ("; A2; ПОШУК ("; A2) +1)))

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

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

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

Розділіть комірки в Excel за допомогою миттєвого заповнення

Миттєве заповнення - це нова функція, представлена в Excel 2013.

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

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

Миттєва популяція полягає у виявленні закономірностей та їх тиражуванні для всіх інших клітин.

Ось як можна вилучити ім’я зі списку за допомогою миттєвого заповнення:

- У клітинку B2 введіть ім’я Sihame motawakil (тобто Sihame).

- Вибравши комірку, ви помітите невеликий квадрат у правому кінці виділення комірки. Двічі клацніть на ньому. Це заповнить одне і те ж ім’я у всіх клітинках.

- Коли комірки заповнені, праворуч унизу з’являється піктограма Параметри автозаповнення. Клацніть на нього.

- Виберіть миттєве заповнення зі списку.

- Як тільки ви оберете миттєве заповнення, ви помітите, що всі комірки оновлюються, і тепер відображається ім'я кожного імені.

Як працює миттєве поповнення ?

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

Якщо в такому випадку я витягну прізвище, миттєве заповнення помилково поверне прізвище у разі відсутності імені. Справедливості заради, це все-таки гарне наближення тенденції. Однак це не те, що я хотів.

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

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

Щоб швидко отримати місто, введіть назву міста за першою адресою (у цьому прикладі введіть Rabat у комірку E3) та використовуйте автозаповнення, щоб заповнити всі комірки. Тепер використовуйте миттєве заповнення і миттєво дасте вам назву міста кожної адреси. Так само ви можете витягти будь-яку частину адреси.

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