Передача тексту до стовпців - це дивовижна функція в Excel, яка заслуговує на набагато більший кредит, ніж зазвичай.
Як випливає з назви, він використовується для поділу тексту на кілька стовпців. Наприклад, якщо у вас є ім’я та прізвище в одній клітинці, ви можете скористатися цим, щоб швидко розділити їх на дві різні клітинки.
Це може бути дійсно корисним, коли ви отримуєте свої дані з баз даних або імпортуєте їх з інших форматів файлів, таких як Text або CSV.
У цьому навчальному посібнику ви дізнаєтесь про багато корисних речей, які можна зробити за допомогою тексту в стовпці в Excel.
Де знайти текст у стовпцях в Excel
Щоб отримати доступ до тексту до стовпців, виберіть набір даних і перейдіть до Дані → Інструменти даних → Текст до стовпців.
Це відкриє майстер перетворення тексту в стовпці.
Цей майстер має три кроки і виконує деякі введення користувача перед тим, як розділити текст на стовпці (ви побачите, як ці різні параметри можна використовувати у наведених нижче прикладах).
Для доступу до тексту в стовпцях також можна скористатися комбінацією клавіш - ALT + A + E.
Тепер давайте зануримось і побачимо деякі дивовижні речі, які ви можете зробити за допомогою Text to Columns в Excel.
Приклад 1 - Розділіть імена на ім’я та прізвище
Припустимо, у вас є набір даних, як показано нижче:
Щоб швидко розділити ім’я та прізвище та отримати їх в окремих клітинках, виконайте наведені нижче дії.
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Розмежоване (це також вибір за замовчуванням). Натисніть Далі.
- На кроці 2 виберіть роздільник як пробіл. Якщо ви підозрюєте, що між іменами можуть бути подвійні/потрійні послідовні пробіли, також оберіть опцію «Розглядати послідовні роздільники як одне». Натисніть Далі.
- На кроці 3 виберіть осередок призначення. Якщо ви не вибрали осередок призначення, він перезапише існуючий набір даних першим ім’ям у першому стовпці та прізвищем у сусідньому стовпці. Якщо ви хочете зберегти вихідні дані недоторканими, створіть копію або виберіть іншу клітинку призначення.
- Натисніть кнопку Готово.
Це миттєво дасть вам результати з іменем в одному стовпці та прізвищем в іншому стовпці.
Примітка:
- Ця техніка добре працює, коли ваше ім’я складається лише з імені та прізвища. Якщо є ініціали або по батькові, це може не спрацювати. Натисніть тут, щоб отримати детальний посібник про те, як вирішувати справи з різними поєднаннями назв.
- Результат, який ви отримуєте від використання функції «Текст у стовпці», є статичним. Це означає, що якщо відбуваються будь -які зміни у вихідних даних, вам доведеться повторити процес, щоб отримати оновлені результати.
Читайте також: Як сортувати за прізвищем у Excel
Приклад 2 - Розділіть ідентифікатори електронної пошти на ім’я користувача та доменне ім’я
Текст у стовпці дозволяє вибрати власний роздільник для розділення тексту.
Це може бути використано для поділу адрес електронної пошти на імена користувачів та доменні імена, оскільки вони розділені знаком @.
Припустимо, у вас є набір даних, як показано нижче:
Це деякі вигадані ідентифікатори електронної пошти деяких крутих супергероїв (крім мене, я просто звичайний хлопець, який витрачає час на Netflix).
Нижче наведено кроки для розділення цих імен користувачів та доменних імен за допомогою функції Text to Columns.
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Розмежоване (це також вибір за замовчуванням). Натисніть Далі.
- На кроці 2 виберіть Інше та введіть @ у полі праворуч від нього. Обов’язково скасуйте вибір будь -якої іншої опції (якщо вона позначена). Натисніть Далі.
- Змініть осередок призначення на той, у якому ви хочете отримати результат.
- Натисніть кнопку Готово.
Це розділить адресу електронної пошти і дасть вам ім’я та прізвище в окремих клітинках.
Приклад 3 - Отримайте кореневий домен з URL -адреси
Якщо ви працюєте з веб -URL -адресами, іноді вам може знадобитися знати загальну кількість унікальних кореневих доменів.
Наприклад, у випадку http://www.google.com/example1 та http://google.com/example2, кореневий домен той самий, що www.google.com
Припустимо, у вас є набір даних, як показано нижче:
Ось кроки, щоб отримати кореневий домен з цих URL -адрес:
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Розмежоване (це також вибір за замовчуванням). Натисніть Далі.
- На кроці 2 виберіть Інше та введіть / (коса коса риса) у полі праворуч від нього. Обов’язково скасуйте вибір будь -якої іншої опції (якщо вона позначена). Натисніть Далі.
- Змініть осередок призначення на той, у якому ви хочете отримати результат.
- Натисніть кнопку Готово.
Це розділить URL -адресу і дасть вам кореневий домен (у третьому стовпці, оскільки перед ним було дві косі риски вперед).
Тепер, якщо ви хочете знайти кількість унікальних доменів, просто видаліть дублікати.
Примітка: Це добре працює, якщо у вас є всі URL -адреси, які мають http: // на початку. Якщо це не так, то ви отримаєте кореневий домен у першому стовпці. Хорошою практикою є узгодження цих URL -адрес перед використанням тексту в стовпцях.
Приклад 4 - Перетворення недійсних форматів дати у допустимі
Якщо ви отримуєте дані з таких баз даних, як SAP/Oracle/Capital IQ, або імпортуєте їх із текстового файлу, існує ймовірність неправильного формату дати (тобто формату, який Excel не вважає датою).
Є лише кілька форматів, які можуть зрозуміти Excel, і будь -який інший формат потрібно перетворити в дійсний формат для використання в Excel.
Припустимо, у вас є дати в наведеному нижче форматі (які не в дійсному форматі дати Excel).
Ось кроки, щоб перетворити їх у формати дійсних дат:
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Розмежоване (це також вибір за замовчуванням). Натисніть Далі.
- На кроці 2 переконайтеся, що вибрано параметр НІ роздільника. Натисніть Далі.
- У форматі даних стовпця виберіть Дата та виберіть потрібний формат (DMY означатиме місяць і рік дати). Також змініть осередок призначення на той, у якому ви хочете отримати результат.
- Натисніть кнопку Готово.
Це дозволить миттєво перетворити ці недійсні формати дат у формати дійсних дат, які можна використовувати в Excel.
Приклад 5 - Перетворення тексту в числа
Іноді, коли ви імпортуєте дані з баз даних або інших форматів файлів, цифри перетворюються у текстовий формат.
Це може статися кількома способами:
- Маючи апостроф перед числом. Це призводить до того, що число розглядається як текст.
- Отримання чисел у результаті текстових функцій, таких як LEFT, RIGHT або MID.
Проблема в цьому полягає в тому, що ці числа (які є у текстовому форматі) ігноруються такими функціями Excel, як SUM та AVERAGE.
Припустимо, у вас є набір даних, як показано нижче, де цифри у текстовому форматі (зверніть увагу, що вони вирівняні ліворуч).
Ось кроки для використання тексту в стовпці для перетворення тексту в числа
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Розмежоване (це також вибір за замовчуванням). Натисніть Далі.
- На кроці 2 переконайтеся, що вибрано параметр НІ роздільника. Натисніть Далі.
- У форматі даних стовпця виберіть загальні. Також змініть осередок призначення на той, у якому ви хочете отримати результат.
- Натисніть кнопку Готово.
Це перетворить ці числа назад у загальний формат, який тепер можна використовувати у формулах.
Приклад 6 - Вилучення перших п’яти символів рядка
Іноді може знадобитися вилучити перші кілька символів рядка. Це може бути випадок, коли у вас є дані про транзакції, і перші п’ять символів (або будь -яка інша кількість символів) представляють унікальний ідентифікатор.
Наприклад, у наборі даних, показаному нижче, перші п’ять символів є унікальними для лінійки продуктів.
Нижче наведено кроки для швидкого вилучення перших п’яти символів з цих даних за допомогою тексту в стовпці:
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 переконайтеся, що вибрано Фіксована ширина. Натисніть Далі.
- На кроці 2 у розділі Попередній перегляд даних перетягніть вертикальну лінію та розмістіть її після 5 символів у тексті. Натисніть Далі.
- Змініть осередок призначення на той, у якому ви хочете отримати результат.
- Натисніть кнопку Готово.
Це розділить ваш набір даних і дасть вам перші п’ять символів кожного ідентифікатора транзакції в одному стовпці, а всі інші у другому стовпці.
Примітка: Ви також можете встановити кілька вертикальних рядків, щоб розділити дані на більш ніж 2 стовпці. Просто клацніть будь -де в області попереднього перегляду даних і перетягніть курсор, щоб встановити роздільник.
Приклад 7 - Перетворення чисел із знаком завершення мінуса у від’ємні числа
Хоча це не те, з чим можна зустрітись часто, але іноді ви можете виявити, що виправляєте числа зі знаками мінуса і робите ці числа від'ємними.
Текст у стовпці - це ідеальний спосіб це відсортувати.
Припустимо, у вас є набір даних, як показано нижче:
Нижче наведено кроки для перетворення цих минулих мінусів у від’ємні числа:
- Виберіть набір даних.
- Перейдіть до Дані → Інструменти даних → Текст у стовпці. Відкриється майстер перетворення тексту в стовпці.
- На кроці 1 натисніть «Далі».
- На кроці 2 натисніть «Далі».
- На кроці 3 натисніть кнопку Додатково.
- У діалоговому вікні Розширені налаштування імпорту тексту виберіть параметр "Остаточний мінус для від'ємного числа". Натисніть OK.
- Встановіть осередок призначення.
- Натисніть кнопку Готово.
Це миттєво поставить знак мінус від кінця числа на початку. Тепер ви можете легко використовувати ці числа у формулах та обчисленнях.