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