Skip to main content

Opposite of Concatenate (split cells) in Excel – 4 easy ways

The Concatenate function in Excel is used to merge the contents of multiple cells into a single cell, but sometimes you might need to do the opposite, which is to split the contents of one cell into multiple cells. This operation is very useful for data analysis and organization. This article will introduce four simple methods to achieve this, helping you manage your data more effectively in Excel, whether you are a beginner or an advanced user.

Perform the opposite of CONCATENATE in Excel

Perform the opposite of CONCATENATE with formulas

To perform the opposite of the CONCATENATE function, you can use formulas that help split text strings into multiple cells based on a specific delimiter.

  1. Enter or copy the following formula into a blank cell where you want to put the result, then, drag the fill handle right to the cells to fill this formula, and the cell values in cell A2 have been split into separated cells, see screenshot: 
    =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
    Note: In the above formula, you can also split the cell values into multiple cells using different separators. Simply replace the space between the first quotes with the delimiter of your choice.
  2. Then, continue dragging the fill handle down to the range of cells where you want to apply this formula. Doing so will split all cell values into separate columns as following screenshot shown:
Tip: Formula for Excel 365

If you're using Excel 365, you'll find a simpler and more efficient function - TEXTSPLIT which is available for dividing data into multiple columns.

  1. Please apply the following formula, after pressing Enter key, each item separated by a space goes into an individual column automatically, see screenshot: 
    =TEXTSPLIT(A2, " ")
  2. Next, select the cells containing the result and then drag the fill handle downwards to apply the formula and obtain the remaining results, see screenshot:

Perform the opposite of CONCATENATE with a powerful feature

Kutools for Excel provides a user-friendly Split Cells feature, with this smart feature, you can quickly split the cell contents into multiple columns or rows based on any separator as you need.

Note: If you want to use this Split Cells feature, please download and install Kutools for Excel first.

After downloading and installing Kutools for Excel, then, click Kutools > Merge & Split > Split Cells to enable this Split Cells feature. In the dialog box, please set the options as below:

  1. Click button to select the data list that you want to split from the Split range section;
  2. Under the Type section, select Split to Columns option;
  3. From the Split by section, please choose the separator that you want to split the cell content based on. Here, I will choose Space;
  4. Then, click OK button;
  5. In another prompt box, click a cell where you want to put the result;
  6. Click OK to get the result.
Note: To apply this feature, please download and install Kutools for Excel first.

Perform the opposite of CONCATENATE with Flash Fill feature

Excel 2013 and later versions introduced a remarkable feature known as Flash Fill. This tool not only helps you automatically fill cells with data but also enables you to split cell contents seamlessly. In this section, I'll explore how to use the Flash Fill feature to split data in Excel.

  1. In an adjacent cell (B2) next to your original data column where you wish to split the data, typing the desired first name-Lucky. Then, press Enter key to go to the next cell. See screenshot:
  2. Then, press Ctrl + E to populate the rest of the cells, see screenshot:
  3. Follow the same procedure for the Last Name and Birthday columns, and you will get the result as demo below:
Notes:
  • Flash Fill requires the output data to be adjacent to the original data without any blank columns in between.
  • The results obtained from the Flash Fill feature are static, meaning that if there are updates to the original data, you will need to rerun the feature to reflect those changes.
  • To know more information of this Fill Flash feature, please view this Master Excel Data Entry with Flash Fill tutorial.

Perform the opposite of CONCATENATE with Text to Column feature

Actually, in Excel, the Text to Columns feature is opposite of the concatenate function, you can apply it to split cell values into multiple cells by any separators you need.

  1. Select the cells that you want to split. And then, click Data > Text to Columns, see screenshot:
  2. In Step 1 of the Convert Text To Columns Wizard, select Delimited option, and then, click Next button, see screenshot:
  3. In Step 2 of the wizard, specify the separator that you want to use under the Delimiters option. Here, I will check Space. And then, go on clicking the Next button, see screenshot:
  4. In Step 3 of the wizard, check General under the Column data format, and then select a cell where you want to locate the split values from the Destination field. Finally, click Finish button, see screenshot:
  5. Now, the selected cell values have been split into multiple columns, see screenshot:

Each of these methods caters to different user preferences and data complexity levels, enabling efficient data manipulation and organization in Excel. Whether you need to split names, dates, or any other structured data, these techniques offer valuable solutions for enhancing your Excel skills and improving your data management capabilities. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Related Articles:

  • Split Text Strings by Delimiter into Multiple Rows - 3 Quick Tricks
  • Normally, you can use the Text to Column feature to split cell contents into multiple columns by a specific delimiter, such as comma, dot, semicolon, slash, etc. But, sometimes, you may need to split the delimited cell contents into multiple rows and repeat the data from other columns as below screenshot shown. Do you have any good ways for dealing with this task in Excel? This tutorial will introduce some effective methods to complete this job in Excel.
  • Split a large table into multiple small tables
  • If you have a large worksheet which contains multiple columns and hundreds or thousands of rows data, now, you want to split this large table into multiple small tables based on the column value or number of rows to get the following results. How could you deal with this task in Excel?
  • Split full name to first and last name
  • Supposing you have a name roster as the first screen shot shows in single column below, and you need to split the full name to the first name column， middle name column and last name column as the following screenshot shown. Here are some tricky methods to help you solve this problem.
  • Split text into separate columns by capital letter
  • Supposing, you have a range of data that you want to split the cell contents into separate columns by the capital letter as the following screenshot shown, do you have any good ideas to solve this problem in Excel?
Comments (16)
No ratings yet. Be the first to rate!
Nasser Emami
Nasser Emami
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
Reply
0
0
skyyang
skyyang     Nasser Emami
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!
Reply
Report
0
0
Nasser Emami
Nasser Emami     skyyang
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.
Reply
0
0
skyyang
skyyang     Nasser Emami
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.
Reply
Report
0
0
David
David
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?
Reply
0
0
David
David
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?
Reply
0
0
Suresh
Suresh
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
Reply
0
0
Raj Kumar
Raj Kumar
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
Reply
0
0
swapni
swapni
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
Reply
0
0
swapni
swapni
This comment was minimized by the moderator on the site
you are A Rong Talling thissss formula
Reply
0
0
jackie t
jackie t
This comment was minimized by the moderator on the site
Excellent directions!
Reply
0
0
Takawira Dirani
Takawira Dirani
This comment was minimized by the moderator on the site
oh snap, i got it above...using text to columns then split option = 'space'
Reply
0
0
Takawira Dirani
Takawira Dirani
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?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations