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

• By using Formulas
• By using a powerful feature-Kutools for Excel
• By using Flash Fill feature
• By using Text to Column feature

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:
   

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.

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.
   
   
   

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:
   

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: