Перейти до основного матеріалу

Навпаки Concatenate (розбиті клітинки) в Excel – 4 простих способи

Функція Concatenate в Excel використовується для об’єднання вмісту кількох клітинок в одну клітинку, але іноді вам може знадобитися зробити навпаки, тобто розділити вміст однієї клітинки на кілька клітинок. Ця операція дуже корисна для аналізу та організації даних. У цій статті ми познайомимося з чотирма простими методами досягнення цієї мети, які допоможуть вам ефективніше керувати даними в Excel, незалежно від того, чи є ви початківцем, чи досвідченим користувачем.

Виконайте дії, протилежні CONCATENATE в Excel


Виконайте дії, протилежні CONCATENATE з формулами

Щоб виконати функцію, протилежну функції CONCATENATE, можна використовувати формули, які допомагають розділити текстові рядки на кілька клітинок на основі певного розділювача.

  1. Введіть або скопіюйте наведену нижче формулу в порожню клітинку, куди потрібно розмістити результат, а потім перетягніть маркер заповнення прямо до клітинок, щоб заповнити цю формулу, а значення клітинок у клітинці A2 буде розділено на окремі клітинки, див. знімок екрана:
    =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
    примітки: у наведеній вище формулі ви також можете розділити значення клітинок на кілька клітинок за допомогою різних роздільників. Просто замініть пробіл між першими лапками з роздільником на ваш вибір.
  2. Потім продовжуйте перетягувати маркер заповнення вниз до діапазону клітинок, до якого потрібно застосувати цю формулу. Це призведе до розділення всіх значень клітинок на окремі стовпці, як показано на наступному знімку екрана:
Порада: Формула для Excel 365

Якщо ви використовуєте Excel 365, ви знайдете простішу та ефективнішу функцію – TEXTSPLIT який доступний для поділу даних на кілька стовпців.

  1. Будь ласка, застосуйте наступну формулу після натискання Що натомість? Створіть віртуальну версію себе у кожен елемент, розділений пробілом, автоматично потрапляє в окремий стовпець, див. знімок екрана:
    =TEXTSPLIT(A2, " ")
  2. Далі виберіть клітинки, що містять результат, а потім перетягніть маркер заповнення вниз, щоб застосувати формулу та отримати решту результатів, див. знімок екрана:

Виконайте дії, протилежні CONCATENATE, за допомогою потужної функції

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

примітки: Якщо ви хочете використовувати це Розділені клітини функція, будь ласка завантажте та встановіть Kutools для Excel перший.

Після завантаження та встановлення Kutools для Excel, потім натисніть Кутулс > Злиття та розділення > Розділені клітини щоб увімкнути це Розділені клітини функція. У діалоговому вікні встановіть параметри, як показано нижче:

  1. Натисніть кнопку, щоб вибрати список даних, який потрібно відокремити Розділити діапазон розділ;
  2. Відповідно до тип розділ, виберіть Розділити на стовпці варіант;
  3. Від Розділено на виберіть роздільник, на основі якого потрібно розділити вміст комірки. Ось я виберу Простір;
  4. Потім натисніть кнопку OK кнопка;
  5. В іншому вікні підказки клацніть клітинку, куди потрібно помістити результат;
  6. Натисніть OK щоб отримати результат.
примітки: Щоб застосувати цю функцію, будь ласка завантажте та встановіть Kutools для Excel перший.

Виконайте дії, протилежні CONCATENATE, за допомогою функції Flash Fill

Excel 2013 і новіші версії представили чудову функцію, відому як Flash Fill. Цей інструмент не лише допомагає автоматично заповнювати комірки даними, але й дає змогу плавно розділяти вміст комірок. У цьому розділі я розповім, як використовувати функцію Flash Fill для розділення даних у Excel.

  1. У сусідній комірці (B2) поруч із початковим стовпцем даних, де ви хочете розділити дані, введіть потрібне ім’я – Лакі. Потім натисніть Що натомість? Створіть віртуальну версію себе у клавіша для переходу до наступної комірки. Перегляньте скріншот:
  2. Потім натисніть Ctrl + E щоб заповнити решту клітинок, перегляньте знімок екрана:
  3. Виконайте ту саму процедуру для стовпців «Прізвище» та «День народження», і ви отримаєте результат, наведений нижче.
Примітки:
  • Flash Fill вимагає, щоб вихідні дані були поруч із вихідними даними без порожніх стовпців між ними.
  • Результати, отримані за допомогою функції Flash Fill, є статичними, а це означає, що якщо є оновлення вихідних даних, вам потрібно буде повторно запустити функцію, щоб відобразити ці зміни.
  • Щоб дізнатися більше про цю функцію Fill Flash, перегляньте це Оволодійте введенням даних Excel із Flash Fill навчальний посібник.

Виконайте дії, протилежні CONCATENATE, за допомогою функції Text to Columne

Насправді в Excel функція «Текст у стовпці» протилежна функції об’єднання, ви можете застосувати її, щоб розділити значення клітинок на кілька клітинок будь-якими потрібними роздільниками.

  1. Виберіть комірки, які потрібно розділити. А потім натисніть дані > Текст у стовпці, див. скріншот:
  2. In Крок 1 Майстра перетворення тексту на стовпцівиберіть Розмежовано , а потім натисніть МАЙБУТНІ , див. знімок екрана:
  3. In Крок 2 майстра, вкажіть роздільник, який ви хочете використовувати під Делімітери варіант. Ось я перевірю Простір. А потім продовжуйте клацати МАЙБУТНІ , див. знімок екрана:
  4. In Крок 3 майстра, перевірте Генеруватил під Формат даних стовпця, а потім виберіть клітинку, у якій потрібно розмістити розділені значення призначення поле. Нарешті натисніть обробка , див. знімок екрана:
  5. Тепер вибрані значення клітинок розділено на кілька стовпців, див. знімок екрана:

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


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

  • Розділіть текстові рядки роздільником на кілька рядків – 3 швидких прийоми
  • Зазвичай ви можете використовувати функцію «Текст у стовпець», щоб розділити вміст клітинки на кілька стовпців за певним роздільником, таким як кома, крапка, крапка з комою, скісна риска тощо. Але інколи вам може знадобитися розділити вміст клітинки на кілька рядків. і повторіть дані з інших стовпців, як показано на знімку екрана нижче. Чи є у вас хороші способи вирішення цього завдання в Excel? Цей підручник познайомить вас з деякими ефективними методами виконання цієї роботи в Excel.
  • Розбийте велику таблицю на кілька маленьких
  • Якщо у вас є великий аркуш, який містить кілька стовпців і сотні чи тисячі даних рядків, зараз ви хочете розділити цю велику таблицю на кілька маленьких таблиць на основі значення стовпця або кількості рядків, щоб отримати такі результати. Як ви могли вирішити це завдання в Excel?
  • Поділіть повне ім’я на ім’я та прізвище
  • Припустимо, у вас є список імен, як показано на першому знімку екрана в одному стовпці нижче, і вам потрібно розділити повне ім'я на стовпець з першим іменем, стовпцем із середнім іменем та стовпцем прізвища, як показано на наступному знімку екрана. Ось кілька хитрих методів, які допоможуть вам вирішити цю проблему.
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Many thanks for your wonderful website and the helpful solutions.
I am using a form on my website to collect CVs and the excel output is like below:

Degree | College Name | Discipline | Year of Graduation | GPA
Masters | Oxford | Mathematics | 2020 | 88
Bachelors | Cambridge | Chemistry | 2016 | 76
Diploma | George's School | Arts | 2012 | 94

All the above lies in a single cell in excel / CSV file.

Can anybody please help me how I can rearrange all this into an excel table with each cell showing part of the entries.

I appreciate your solutions.

best regards,
Nasser
This comment was minimized by the moderator on the site
Hello, Emami

To split the data you provided, both the formula and Text to Column feature can help you. please do as this:
Method1: formula: (After pasting the formula, drag the fill handle right to get the first line result, and then drag the fill handle down to get other results.)
=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-reverse-concatenate.png

Method2: Text to Column:
By using Text to Column feature step by step, and in step3, please enter | character into Other box:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-reverse-concatenate-2.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Dear skyyang,
Many thanks for the reply.
First, I cannot use the text to column method because this process has to be done automatically by a formula.
Second, the problem is the table output I receive from csv file (the form entry) is saved in a single cell not in three rows.
ie. All three rows (A1:A4) are stored in one single cell not 4 cell in 4 rows.
I hope you understand my problem and can provide me with a solution.

thanks.
This comment was minimized by the moderator on the site
Hello, Emami,
If your data in a single cell, first, you should split the data into multiple rows based on the line breaks. to split the data into rows, please use this code:
Sub SplitCells()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    lLFs = VBA.Len(Rng) - VBA.Len(VBA.Replace(Rng, vbLf, ""))
    If lLFs > 0 Then
        Rng.Offset(1, 0).Resize(lLFs).Insert shift:=xlShiftDown
        Rng.Resize(lLFs + 1).Value = Application.WorksheetFunction.Transpose(VBA.Split(Rng, vbLf))
    End If
Next
End Sub


Applying the code to split the data into multiple rows, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-split-data.png

Then, you can use this formula to split them into multiple columns as you need.
=TRIM(MID(SUBSTITUTE($A1,"|",REPT(" ",999)),COLUMNS($A:A)*999-998,999))


Please have a try, hope it can help you, if you have any other problem, please comment here.
This comment was minimized by the moderator on the site
I believe it has something to do with having over a certain amount of value in a cell. How does one use this formula with large strings?
This comment was minimized by the moderator on the site
Thank you for this. I had a strange occurence where two strings of text produced #Value!, while all the rest was fine. Any idea why this would happen?
This comment was minimized by the moderator on the site
just want to know if in a cell content i.e (123). can it be splitted to multiple column seperately as cell1 as 1, cell2 as 2 and cell3 as 3
This comment was minimized by the moderator on the site
my query is below
from given data
A B
1 Capital Account Capital Account 6,62,73,000.00
2 SHARE CAPITAL A/C SHARE CAPITAL A/C 67,17,300.00
3 SHARE PREMIUM A/C SHARE PREMIUM A/C 5,95,55,700.00


I need in column C (C + B-A) i.e
C
1 6,62,73,000.00

2 67,17,300.00

3 5,95,55,700.00
This comment was minimized by the moderator on the site
sorry I m not see perfect in first time
this formula is perfect and correct
This comment was minimized by the moderator on the site
you are A Rong Talling thissss formula
This comment was minimized by the moderator on the site
Excellent directions!
This comment was minimized by the moderator on the site
oh snap, i got it above...using text to columns then split option = 'space'
This comment was minimized by the moderator on the site
Thank you so much, helpful indeed. What if there is no comma delimiter in between the words like, "Steve Simpson" and you still want to split?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations