How☝️ to Do the Opposite of Concatenate in Excel

In Excel, the Concatenate function allows you to join together two or more strings of text into a single cell. This function is reversible.

To reverse the Concatenate function in Excel, click on the cell where you want to see the result and enter the following formula:

=TRIM(MID(SUBSTITUTE($A2,”,”,REPT(” “,999)),COLUMNS($A:A)*999-998,999))

In this formula, A2 is the cell with the combined text. Press the Enter key and drag the cell with its result to the right to make all the other values appear.

3 Ways to Reverse the Concatenate Function in Excel

When you reverse the Concatenate function in Excel, you end up splitting one cell into several cells. This can be useful when you want to unmerge data from one cell.

Here we provide three distinct approaches to this task. Let’s take a closer look at each method.

Method 1: How to Reverse the Concatenate Function in Excel Using a Formula

1. Click on the cell where you want the result. 

2. Go to the Formula bar and type the following formula: 

=TRIM(MID(SUBSTITUTE($A2,”,”,REPT(” “,999)),COLUMNS($A:A)*999-998,999))  

A2 is the cell containing the text you need to split.

How to Reverse the Concatenate Function in Excel Using a Formula

3. Press the Enter key on your keyboard.

4. Drag the cell with the formula to the right so the other values appear in their own cells. 

The data

And there you go! You have split the text from one cell into multiple cells.

NOTE: This only works if the data is separated by commas.

Method 2: How to Reverse the Concatenate Function in Excel with the Text-to-Columns Option

1. Select the data you need to split into multiple cells.

2. Navigate to the Data tab.

3. Pick the Text to Columns option.

Text-to-Columns Option

4. Choose Delimited as the file type that best describes your data.

5. Click Next.

Delimited option

6. Mark the Comma Delimiter to remove commas from the rows and separate your data. The commas are used to tell the program where to separate the data.

7. Click Next.

Comma Delimiter

8. Write the Destination for your data. Here we set a destination of =$B$2:$D$5, where B2:D5 is the range of cells where we want the result to go. 

If you prefer, you can also right-click and drag the cursor over the cells on the spreadsheet to select the range. 
9. Click Finish.

Destination Formula

10. Confirm your action by selecting OK.

The Ok tab

Voila! You’ve successfully done it!

Excel Data

Method 3: How to Split Text Using the Flash Fill Option in Excel

1. To split the text in the first cell (A2), click on the cell where you wish to start the split (B2) and enter the first word manually.

How to Split Text Using the Flash Fill Option in Excel

2. Click and drag from the bottom right corner of that cell to the right until the other cells are filled with their values.

Dragging the Data

It’s very easy! To apply this function to each row, simply repeat the process. 

Excel Data

Here is our result!

NOTE: This only works for lists that Excel can extrapolate on its own, such as a list of months or days or numbers in order.

Reversing the Concatenate Function in Excel FAQs

What is the Opposite of Concatenation?

The opposite function of the Concatenation function splits a text into parts rather than joining them together into one long row. This could be useful in certain situations where you need to manipulate or extract specific data from a row of text.

What Are Some Uses for Reversing the Concatenate Function in Excel?

Reversing the Concatenate function in Excel could help you separate names into first and last name columns, separate address information into multiple cells, or create a list of items from a single cell.

What Are the Benefits of Reversing the Concatenate Function in Excel?

There are several benefits to using the opposite function of concatenate in Excel:

  • Save Time: It can save you time by reducing the number of steps you need to take to unmerge data. 
  • Better Manipulation and Analysis of Your Data: If you have a list of names in one cell, you can use the reverse of concatenation to split them into first and last name columns. This can be helpful for sorting or filtering your data. 
  • Avoid Mistakes: It can help you avoid the errors that can occur when unmerging data manually.
More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X