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

Розділіть клітинки в Excel на кілька стовпців

• З майстром «Текст у стовпець».
• Легко з Kutools
• З Flash Fill (Excel 2013 і новіші версії)
• З формулами

Розбийте клітинки в Excel на кілька рядків

• З функцією TEXTSPLIT (Excel 365)
• Легко з Kutools
• З кодом VBA
• з Power Query (Excel 2016 і новіші версії)

Відео

Розділіть клітинки в Excel на кілька стовпців

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

Розділіть клітинки на кілька стовпців за допомогою майстра «Текст у стовпець».

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

Крок 1. Виберіть комірки, які потрібно розділити, і відкрийте майстер «Текст у стовпці».

У цьому випадку я вибираю діапазон A2: A8, який містить повні імена. Потім перейдіть до дані вкладка, клацніть Текст у стовпці відкрити Текст у стовпці майстра.

Крок 2. Налаштуйте кроки один за одним у майстрі

1. У Крок 1 з 3 майстра, виберіть Розмежовано а потім натисніть кнопку Далі кнопки.
   

   

2. У Крок 2 з 3 виберіть роздільники для ваших даних, а потім клацніть Далі Кнопка продовжити.
   
   У цьому випадку, оскільки мені потрібно розділити повні імена на імена та прізвища на основі пробілів, я вибираю лише Простір у полі Делімітери .

   

   примітки:

   • Якщо потрібний вам роздільник не відображається в цьому розділі, ви можете вибрати Other прапорець і введіть власний роздільник у текстове поле.
   • Щоб розділити клітинки за розривом рядка, ви можете вибрати Other прапорець і натисніть Ctrl + J ключі разом.
     
     
3. В останньому майстрі потрібно налаштувати наступним чином:
   
   1) У призначення виберіть клітинку для розміщення розділених даних. Тут я вибираю клітинку C2.

   2) Клацніть на обробка кнопки.

   

Результат

Повні імена у вибраних клітинках розділені на імена та прізвища та розташовані в різних стовпцях.

Зручно розділяйте клітинки на кілька стовпців за допомогою Kutools

Як ви можете бачити, Текст у стовпці Майстер вимагає кількох кроків для виконання завдання. Якщо вам потрібен більш простий метод, то Розділені клітини особливість Kutools for Excel настійно рекомендується. За допомогою цієї функції ви можете зручно розділити клітинки на кілька стовпців або рядків на основі певного розділювача, виконавши налаштування в одному діалоговому вікні.

після установки Kutools for Excelвиберіть Кутулс > Злиття та розділення > Розділені клітини відкрити Розділені клітини діалогове вікно.

1. Виділіть діапазон клітинок, що містять текст, який потрібно розділити.
2. Виберіть Розділити на стовпці варіант.
3. Select Простір (або будь-який потрібний роздільник) і натисніть OK.
   
   
4. Виберіть комірку призначення та клацніть OK щоб отримати всі розділені дані.
   
   

Розділіть клітинки на кілька стовпців за допомогою Flash Fill

Тепер перейдемо до третього способу, відомого як Флеш-заповнення. Представлено в Excel 2013, Флеш-заповнення призначений для автоматичного заповнення ваших даних, коли він відчуває шаблон. У цьому розділі я покажу, як використовувати функцію Flash Fill, щоб відокремити імена та прізвища від повних імен в одному стовпці.

Крок 1: вручну введіть перші розділені дані в клітинку, прилеглу до вихідного стовпця

У цьому випадку я збираюся розділити повні імена в стовпці А на окремі імена та прізвища. Перше повне ім’я міститься в клітинці A2, тому я вибираю сусідню з нею клітинку B2 і вводжу ім’я. Перегляньте скріншот:

Крок 2. Застосуйте Flash Fill, щоб автоматично заповнити всі імена

Почніть вводити друге ім’я в клітинку під B2 (це B3), тоді Excel розпізнає шаблон і створить попередній перегляд решти імен, і вам потрібно натиснути Що натомість? Створіть віртуальну версію себе у щоб прийняти попередній перегляд.

Тепер усі імена та повні імена в колонці A розділені в колонці B.

Крок 3: Отримайте прізвища повних імен в іншій колонці

Вам потрібно повторити описані вище кроки 1 і 2, щоб розділити прізвища від повних імен у стовпці A в стовпець поруч із стовпцем імені.

Результат

Розділіть клітинки на кілька стовпців із формулами

Наведені вище методи не є динамічними, а це означає, що якщо вихідні дані змінюються, нам потрібно повторно запустити той самий процес. Візьміть той самий приклад, що й вище, щоб розділити список повних імен у стовпці A на окремі імена та прізвища та автоматично оновлювати розділені дані з будь-якими змінами у вихідних даних, спробуйте одну з наведених нижче формул.

Використовуйте функції TEXT, щоб розділити клітинки на стовпці за певним роздільником

Формули, наведені в цьому розділі, доступні в усіх версіях Excel. Щоб застосувати формули, виконайте наступне.

Крок 1. Витягніть текст перед першим роздільником (імена в цьому випадку)

1. Виберіть клітинку (у цьому випадку C2), щоб вивести ім’я, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати перше ім’я в A2.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   
2. Виберіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати решту імен.
   
   

Крок 2. Витягніть текст після першого роздільника (прізвища в цьому випадку)

1. Виберіть клітинку (у цьому випадку D2), щоб вивести прізвище, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати прізвище в А2.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   
2. Виберіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати решту прізвищ.
   
   

Використовуйте функцію TEXTSPLIT, щоб розділити клітинки на стовпці за певним роздільником

Якщо ви використовуєте Excel для Microsoft 365, Функція TEXTSPLIT є більш рекомендованим. Будь ласка, зробіть наступне.

Крок 1: виберіть клітинку для виведення результату. Тут я вибираю клітинку C2

Крок 2: Введіть наведену нижче формулу та натисніть Enter

=TEXTSPLIT(A2," ")

Ви бачите, що весь текст, розділений пробілами в A2, розділений на різні стовпці.

Крок 3. Перетягніть формулу, щоб отримати всі результати

Виберіть клітинки результатів у тому самому рядку, а потім перетягніть ручку автозаповнення вниз, щоб отримати всі результати.

Розбийте клітинки в Excel на кілька рядків

Як показано на знімку екрана нижче, у діапазоні A2:A4 є список деталей замовлення, і дані потрібно розділити за допомогою скісної риски, щоб отримати різні типи інформації, як-от елемент, кількість, ціна за одиницю та дата. Щоб виконати це завдання, у цьому розділі демонструються 3 методи.

Розділіть клітинки на кілька рядків за допомогою функції TEXTSPLIT

Якщо ви використовуєте Excel для Microsoft 365, метод функції TEXTSPLIT може легко допомогти. Будь ласка, зробіть наступне.

Крок 1: виберіть клітинку для виведення результату. Тут я вибираю клітинку B6

Крок 2: Введіть наведену нижче формулу та натисніть Enter

=TEXTSPLIT(A2,,"/")

Весь текст у форматі A2 розбивається на окремі рядки за допомогою роздільника «скісна риска».

Щоб розділити дані в клітинках A3 і A4 на окремі рядки на основі косих риск, просто повторіть кроки 1 і 2 з відповідними формулами, наведеними нижче.

Формула в C6:

=TEXTSPLIT(A3,,"/")

Формула в D6:

=TEXTSPLIT(A4,,"/")

Результат

Зручно розділяйте клітинки на кілька рядків за допомогою Kutools

Хоча функція TEXTSPLIT Excel дуже корисна, вона обмежена користувачами Excel для Microsoft 365. Крім того, якщо у вас є кілька клітинок у стовпці, які потрібно розділити, вам потрібно буде застосувати різні формули окремо до кожної клітинки, щоб отримати результати. У контрасті, Kutools for Excel's Розділені клітини функція працює в усіх версіях Excel. Він забезпечує просте та ефективне рішення для одночасного поділу клітинок на кілька рядків або стовпців лише кількома клацаннями.

після установки Kutools for Excel, Натисніть Кутулс > Злиття та розділення > Розділені клітини відкрити Розділені клітини діалогове вікно.

1. Виділіть діапазон клітинок, що містять текст, який потрібно розділити.
2. Виберіть Розділити на ряди варіант.
3. Виберіть потрібний роздільник (тут я вибираю Other і введіть косу риску), а потім натисніть OK.
   
   
4. Виберіть комірку призначення та клацніть OK щоб отримати всі розділені дані
   
   

Розділіть клітинки на кілька рядків за допомогою коду VBA

У цьому розділі наведено код VBA, за допомогою якого ви можете легко розділити клітинки на кілька рядків у Excel. Будь ласка, зробіть наступне.

Крок 1. Відкрийте вікно Microsoft Visual Basic for Applications

Натисніть інший + F11 клавіші, щоб відкрити це вікно.

Крок 2: Вставте модуль і введіть код VBA

Натисніть Insert > Модулі, а потім скопіюйте та вставте наведений нижче код VBA у Модуль (Код) вікна.

Код VBA: розділіть клітинки на кілька рядків у Excel

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Крок 3. Запустіть код VBA

Натисніть F5 ключ для запуску коду. Потім вам потрібно виконати наступні налаштування.

1. З’явиться діалогове вікно, у якому вам буде запропоновано вибрати клітинки з даними, які потрібно розділити (тут я вибираю діапазон A2:A4). Зробивши вибір, натисніть OK.
   
   
2. У другому спливаючому діалоговому вікні вам потрібно вибрати вихідний діапазон (тут я вибираю клітинку B6), а потім клацнути OK.
   
   
3. В останньому діалоговому вікні введіть роздільник, який використовується для розділення вмісту комірки (тут я вводжу косу риску), а потім натисніть значок OK кнопки.
   
   

Результат

Комірки у вибраному діапазоні розбиваються на кілька рядків одночасно.

Розбийте клітинки на декілька рядків за допомогою Power Query

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

Крок 1: Виберіть клітинки, які потрібно розділити на кілька рядків, виберіть «Дані» > «З таблиці / діапазону».

Крок 2. Перетворіть вибрані клітинки на таблицю

Якщо вибрані комірки не є форматом таблиці Excel, a Створити таблицю з’явиться діалогове вікно. У цьому діалоговому вікні вам просто потрібно перевірити, чи Excel правильно вибрав вибраний діапазон комірок, позначити, чи є у вашій таблиці заголовок, а потім клацнути OK кнопки.
Якщо вибрані клітинки є таблицею Excel, перейдіть до кроку 3.

Крок 3: виберіть «Розділити стовпець за роздільником».

A Стіл - Power Query редактор спливає вікно, клацніть Розділена колонка > За роздільником під Головна Вкладка.

Крок 4: Налаштуйте діалогове вікно «Розділити стовпець за роздільником».

1. У Виберіть або введіть роздільник розділ, вкажіть роздільник для розділення тексту (тут я вибираю виготовлений на замовлення і введіть косу риску / у текстовому полі).
2. Розгорнути Додаткові параметри розділ (який за замовчуванням згорнуто) і виберіть ряди варіант.
3. У Цитата характер розділ, вибрати ніхто зі спадного списку;
4. Натисніть OK.
   
   

Крок 5: Збережіть і завантажте розділені дані

1. У цьому випадку, оскільки мені потрібно вказати спеціальне призначення для моїх розділених даних, я клацаю Закрити та завантажити > Закрити та завантажити до.
   
   
   Чайові: щоб завантажити розділені дані на новому аркуші, виберіть Закрити та завантажити варіант.
2. У Імпортувати дані діалоговому вікні, виберіть Існуючий аркуш виберіть клітинку, щоб знайти розділені дані, а потім клацніть OK.
   
   

Результат

Потім усі комірки у вибраному діапазоні розбиваються на різні рядки в одному стовпці за вказаним роздільником.

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

Статті по темі