Доброго дня шановний читачу! У статті я хочу розглянути питання про те, як і якими способами, можливо, розділити текст у комірці, який був склеєний! Якщо ви часто працюєте з імпортованими даними в Excel, то періодично зустрічаєте такі проблеми як вивантажені точки замість ком, неправильний формат даних, сліплені слова або значення та багато іншого. На цей випадок Excel надає кілька можливостей щодо нормалізації даних і у кожного з них є свої плюси і мінуса.
Розібрати злитий текст на необхідні складові можна зробити за допомогою:
- Майстри розбору текстів;
- Формули;
- Макросів VBA.
Метод 1: об'єднуємо комірки
Цей метод дозволяє створити візуальну видимість поділу комірки шляхом об'єднання сусідніх. Ось як це робиться:
- Спершу необхідно ретельно обміркувати будову таблиці, яку ми хочемо створити.
- Поряд з комірками, які повинні імітувати раніше розділений елемент (припустимо, на дві частини), виділяємо аналогічну кількість сусідніх комірок, у нашому випадку - у верхніх рядках стовпчиків. Потім клацніть на значку «Об'єднати і помістити в центрі» (блок «Вирівнювання», вкладка «Головна»).
- У результаті об'єднання комірок складається відчуття, що в нижніх рядках стовпчик поділяється на 2 частини. Хоча насправді ми бачимо на горизонтальній координатній панелі, що кожній комірці відповідає окреме позначення.
Подібним чином виконується «ділення» комірки на кілька рядків.
Майстер розбору текстів
Розгляньмо найпростіший спосіб розділити текст у комірці, не по суті, а по виконанню. Для прикладу, дуже наочно це можна продемонструвати на ПІБ в одній комірці, які необхідно розділити на окремі стовпчики для зручності сортування і фільтрації.
Для виконання завдання викликаємо діалогове вікно «Майстер текстів (розбір)» і в 3 кроки поділяємо текст:
- Для початку потрібно виділити дані, які необхідно розділити, наступним кроком на вкладці «Дані» в розділі «Робота з даними» натискаєте іконку «Текст по стовпчиках» і у діалоговому вікні майстра вказуємо формат робочого тексту. Вибираємо 2 види форматів:
- З роздільниками - це коли існує текст або символ, який умовно буде відокремлювати майбутнє вміст окремих комірок;
- Фіксована ширина - це коли за допомогою пробілів у тексті імітується стовпчики однакової ширини.
- Другим кроком, у нашому прикладі, вказуємо символ, що виконує роль роздільника. У випадках, коли в тексті йдуть поспіль пару роздільників, кілька пробілів, наприклад, то параметр «Вважати послідовні роздільники одним» вкаже для Excel приймати їх за один роздільник. Додаткова умова «Обмежувач рядків» допоможе вказати, що текстові значення, що містяться в лапках, не ділити (наприклад, назва);
- Останнім кроком, для вже розділених стовпчиків, потрібно вказати в діалоговому вікні майстра, попередньо виділивши їх, вибрати необхідний формат отриманих даних:
- Загальний - не проводить зміни даних, залишаючи їх у початковому вигляді, буде оптимальним вибором у більшості випадків;
- Текстовий - цей формат, в основному, необхідний для стовпчиків з числовими значеннями, які програма в обов'язковому порядку повинна інтерпретувати як текст. (Наприклад, це числа з роздільником по тисячі або номер пластикової картки);
- Дата - цей формат використовується для стовпчиків з датами, до речі, формат самої дати можна вибрати у спадному списку.
Якщо ви використовуєте символи, які не схожі на стандартні, призначені в регіональних параметрах, можете використовувати кнопку «Докладніше» для правильного їх розпізнавання.
Метод 2: розділяємо об'єднані комірки
Якщо ми хочемо, наприклад, виконати точкові ділення комірок таблиці, у цьому випадку виконуємо такі кроки:
- Виділяємо на відповідній панелі координат стовпчики (у нашому випадку) або рядки, комірки яких потім сприйматимуться єдиними. Клацніть на стрілці вниз поруч зі значком «Об'єднати і помістити в центрі» і вибираємо з запропонованих варіантів - «Об'єднати по рядках».
- З двох стовпчиків отримуємо умовний один. Тепер вибираємо комірку, яку потрібно представити як розділену на частини. Клацнемо на ній, потім знову тиснемо на значок «Об'єднати і помістити в src =» https://microexcel.ru/wp-content/uploads/2020/03/razdel-yacheyki-exc-5.png» class=»aligncenter» width=»909″ height=»621″[/img]
- У результаті нам вдалося «розбити» осередок на дві частини, хоча насправді це роз'єднання раніше об'єднаних осередків.
Дії для «ділення» комірки на кілька рядків аналогічні, правда, в цьому випадку об'єднання для кожного стовпчика доведеться робити вручну.
Відео: Формули в Ексель
Далі виділяємо всі три комірки і простягаємо формули вниз і отримуємо потрібний нам результат. На цьому можна закінчити, а можна проміжні розрахунки позиції пробілів прописати в самі формули поділу тексту.
Це дуже просто зробити. Ми бачимо, що розрахунок першого пробілу знаходиться в комірці H2 - ЗНАЙТИ («» -A2-1), а розрахунок другого пробілу в комірці I2 - ЗНАЙТИ («» -A2-H2 + 1).
Бачимо, що у формулі комірки I2 зустрічається H2 міняємо її на саму формулу і отримуємо в комірці I2 вкладену формулу ЗНАЙТИ («» -A2-НАЙТИ («» -A2-1) + 1)
Дивимося першу формулу виділення Прізвища і дивимося де тут зустрічається H2 або I2 і міняємо їх на формули в цих комірках, аналогічно з Ім'ям і Прізвищем
- Прізвище = ПСТР (A2-1-H2-1) отримуємо = ПСТР (A2-1-НАЙТИ («» -A2-1) -1)
- Ім'я = ПСТР (A2-H2 + 1-I2-H2) отримуємо = ПСТР (A2-НАЙТИ («» -A2-1) + 1; ЗНАЙТИ («» -A2-НАЙТИ («» -A2-1) + 1) -ПІТИ («» -A2-1))
- По батькові = ПСТР (A2-I2 + 1-50) отримуємо = ПСТР (A2-НАЙТИ ("" -A2-НАЙТИ ("" "-A2-1) + 1) + 1-50)
Тепер проміжні обчислення позиції пробілом можна сміливо видалити. Це один з прийомів, коли для простоти спочатку шукаються проміжні дані, а потім функцію вкладають одну в іншу. Погодьтеся, якщо писати таку велику формулу відразу, то легко заплутатися і помилитися.
Сподіваємося, що цей приклад показує вам, як корисні текстові функції Excel для роботи з текстом і як вони дозволяють ділити текст автоматично за допомогою формул однотипні дані.
Метод 3: ділимо комірку по діагоналі
Щоб розділити комірку по діагоналі, не потрібно шукати нестандартні ходи, все реалізується через звичайні функції програми:
- Клацніть правою кнопкою миші, щоб розділити комірку. У списку команд, що розкрився, вибираємо «Формат комірок». Або можна зробити простіше - просто натиснути комбінацію Ctrl + 1, попередньо вибравши потрібну комірку.
- На екрані буде показано вікно форматування, в якому нам потрібна вкладка «Межа». Внизу від області «Напис» з правої та лівої сторони ми можемо помітити два значки з косими лініями - клацаємо по тому виду поділу, який потрібен. Якщо потрібно, тут можна вказати тип лінії і вибрати її колір.
Після внесених змін необхідно знову натиснути на кнопку з косою рисою. Результат можна відстежувати тут же, в області «Напис». Коли все готове, клікаємо OK.
- У підсумку отримуємо комірку, розділену по діагоналі. При цьому не забуваємо, що програма як і раніше бачить її, як одну цілу.