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?