Функція “Текст за стовпцями” в Excel дає змогу розподілити текстові дані, що знаходяться в одній клітинці, по різних стовпцях. Ця проста дія здатна значно спростити роботу користувачам, позбавивши їх від ручного розділення текстових даних.
Спершу розглянемо базовий приклад розподілу даних на два окремі стовпці. Потім дослідимо два додаткових способи застосування цієї функції, які можуть бути невідомі більшості користувачів Excel.
Розподіл тексту за наявними роздільниками
Для початку розглянемо розділення тексту за допомогою роздільників. Це найпоширеніший випадок використання цієї функції.
У прикладі нижче маємо список імен, розміщений в одному стовпці. Наша мета – розділити імена та прізвища на окремі стовпці.
Нам потрібно, щоб імена залишилися в стовпці A, а прізвища перемістилися до стовпця B. Оскільки в стовпці B вже є інформація (назва відділу), потрібно спочатку вставити новий стовпець та надати йому заголовок.
Потім потрібно виділити діапазон клітинок з іменами і вибрати в меню “Дані” опцію “Текст за стовпцями”.
Запуститься майстер, який складається з трьох кроків. На першому кроці потрібно визначити, яким чином розділений вміст. Розділений означає, що різні частини тексту розмежовані спеціальним символом, таким як пробіл, кома або коса риска. Саме цей варіант ми тут і оберемо. (Про опцію “фіксована ширина” поговоримо в наступному розділі).
На другому кроці треба вказати символ-роздільник. У нашому випадку імена та прізвища розділені пробілом. Отже, потрібно зняти позначку з опції “Табуляція” і поставити її біля опції “Пробіл”.
На останньому кроці можна налаштувати форматування даних. Для цього прикладу форматування не потрібне, але ви можете вказати, чи дані подані у форматі тексту або дати, а також налаштувати конвертацію одного формату в інший.
Поле “Призначення” залишимо $A$2, щоб розділити текст з поточної позиції, перемістивши прізвища до стовпця B.
Натискаючи “Готово”, Excel розділяє імена та прізвища, і тепер у нас є повністю заповнений стовпець B.
Розподіл тексту фіксованої ширини
Розглянемо приклад розділення тексту, що має фіксовану ширину. У наведених нижче даних маємо код рахунку-фактури, який завжди складається з двох літер, за якими слідує певна кількість цифр. Код з двох літер вказує на клієнта, а числове значення – номер рахунку-фактури. Наша мета – відділити перші два символи коду рахунку від чисел і помістити їх у стовпці “Клієнт” і “Номер рахунку”, які ми створили (стовпці B і C). При цьому повний код рахунку-фактури у стовпці А має залишитися незмінним.
Оскільки код рахунку-фактури завжди починається з двох символів, він має фіксовану ширину.
Спершу виділяємо діапазон клітинок з текстом, який потрібно розділити, та обираємо “Дані” > “Текст за стовпцями”.
На першій сторінці майстра потрібно обрати опцію “Фіксована ширина” і натиснути “Далі”.
На наступній сторінці потрібно вказати позиції в стовпці, де потрібно розділити текст. Це можна зробити, клацнувши мишею в області попереднього перегляду.
Примітка: Інколи функція “Текст за стовпцями” пропонує власні варіанти розривів. Це може заощадити час, але слід бути уважними, оскільки запропоновані варіанти не завжди можуть бути правильними.
В області “Попередній перегляд даних” потрібно клацнути в місці, де ви хочете зробити розрив, і потім натиснути “Далі”.
На останньому кроці вказуємо клітинку B2 (=$B$2) в полі “Призначення” і натискаємо “Готово”.
Номери рахунків-фактур успішно розділені на стовпці B і C, а вихідні дані залишаються в стовпці A.
Отже, ми розглянули розділення тексту за допомогою роздільників та фіксованої ширини. Також ми побачили, як розділяти текст на місці та розбивати його на різні місця в таблиці. Тепер подивимось на два додаткові способи використання функції “Текст за стовпцями”.
Перетворення дат з американського формату в європейський
Однією з корисних можливостей “Текст за стовпцями” є перетворення форматів дат, наприклад, з американського в європейський чи навпаки.
Коли я, як мешканець Великої Британії, імпортую дані в Excel, вони іноді зберігаються як текст. Це тому, що вихідні дані можуть бути з США, а формати дат не відповідають регіональним налаштуванням моєї версії Excel.
В цьому випадку, функція “Текст за стовпцями” допоможе перетворити дати. Нижче показано приклади дат в американському форматі, які Excel неправильно розуміє.
Спершу виділяємо діапазон клітинок з датами, які потрібно перетворити, і обираємо “Дані” > “Текст за стовпцями”.
На першій сторінці майстра залишаємо опцію “з роздільниками”, а на другій знімаємо позначки з усіх роздільників, оскільки ми не хочемо нічого розділяти.
На останній сторінці потрібно вибрати опцію “Дата” та вказати у списку формат отриманих даних. У цьому випадку обираємо “MDY”, формат, типовий для США.
Після натискання кнопки “Готово” дати успішно перетворені і готові до подальшого використання.
Перетворення міжнародних числових форматів
Крім перетворення форматів дат, функція “Текст за стовпцями” здатна конвертувати міжнародні числові формати.
У Великій Британії для десяткових значень використовується крапка. Тобто, число 1064,34 означає трохи більше тисячі.
У багатьох країнах замість крапки використовується кома. Тому це число буде неправильно інтерпретоване Excel і збережеться як текст, тобто буде подане як 1.064,34.
На щастя, функція “Текст за стовпцями” допоможе нам перетворити ці значення.
У прикладі нижче маємо перелік чисел, відформатованих з використанням десяткової коми, тому Excel не розпізнає їх, відповідно до регіональних налаштувань.
Процес майже ідентичний тому, який ми використовували для перетворення дат. Виділяємо діапазон значень, переходимо до “Дані” > “Текст за стовпцями”, вибираємо опцію “з роздільниками” і видаляємо всі роздільники. На останньому кроці вибираємо опцію “Загальний” та натискаємо кнопку “Додатково”.
У вікні налаштувань, що відкриється, потрібно ввести відповідні символи для роздільника тисяч і десяткової коми. Натискаємо “ОК”, а потім “Готово”, коли повернемось до майстра.
Значення перетворено і тепер розпізнаються як числа, що дозволяє використовувати їх для подальших обчислень та аналізу.
Функція “Текст за стовпцями” має більше можливостей, ніж люди думають. Її класичне використання для поділу вмісту на різні стовпці надзвичайно корисне, особливо під час обробки даних, отриманих від інших. А менш відомі можливості для перетворення форматів дат і міжнародних чисел дійсно вражають.