What is the opposite of concatenate in Excel (+How to use it)

Sometimes we need to do the opposite and split up the joined text in one cell to many cells. There is no straightforward function or formula available for this, therefore if we wish to use a formula then we have to create a customised formula to achieve this goal. However, there is a simpler method in Excel to accomplish the same task which is listed below.

  • Using built-in data manipulation tool “Text to Columns”

Suppose we have a data set containing First Names and Email Addresses separated by “,”. We wish to split up both first names and email addresses from the data. 

Using the Data Tools “Text to Columns”

This method is also very handy and it can split the text data into different columns and we can choose any special character from “Tab”, “Semi colon”, “Comma”, “Space” and any other special character of our choice as well. Let’s dive into it and see how it can be done.

Microsoft Excel provides us with a variety of built-in formulas to manipulate text data. There are some functions to join text data from multiple cells, which are CONCAT and TEXTJOIN. While these formulas help us to join or merge the text from different cells.

Step 1 – Select all the data set

– We’ll use the same data set as before, so just select all the data containing names and email addresses.

Step 2 – Locate the Text to Columns Option on Data Tools

– On the Data tab, go to the Data Tools group and locate the Text to Columns option.

Step 3 – Choose appropriate data type

– Click on the “Text to Columns” option and a new dialog box will open up. It will ask you to choose the data type. Since our data is delimited with comma, so we’ll select the “delimited” option.

Step 4 – Choose the Data Delimiter

– Click on the “Next” option and we’ll reach 2 out of 3 to split up the data. 
– It will ask you to choose the data delimiter. Since our data is delimited with commas, we’ll select the “Comma” option.

Step 5 – Choose Output Format and Destination

– In the last step 3 of this wizard to split up the data choose the output format and destination’s first column.

Step 6 – Finally Extract Data

– As the final step, just click the Finish button and you will get the data starting from the column that you chose in the last step.

So we can say that “Text to Columns” is the opposite of concatenating data from multiple cells into one cell.