CONCATENATE Function is a crucial function to structure data according to your needs. Reverse concatenate in Excel is equally important since sometimes you might want to split one cell into multiple cells.
However, there is no such function as unconcatenate in Excel.
In this article, I am going to show you the opposite of concatenate. There are 3 ways to achieve this.
You may also be interested in How to Display the Formula as Text in Excel?
Reverse Concatenate In Excel Example
We will be using these 3 rows of data to illustrate our ideas in this article.
Features of each options (Reverse Concatenate In Excel)
To decide which option to use, you may want to take a look of the below table.
Among all 3 options, I personally prefer formula because of its flexibility. If you foresee yourself changing the source data, I will highly recommend you using the formula.
Although the formula may seem a bit complicated, you are going to thank yourself for using that.
After all, flexibility is key when it comes to data structuring.
| Features | Text to Columns | Formula | Flash Fill |
|---|---|---|---|
| Dynamic? | No, you will need to re-perform it to get a different result | Yes | No, you will need to re-perform it to get a different result |
| Always return result? | Yes | Yes | No |
| Replace source data? | Yes | No | Yes |
| Speed? | Super quick | Take some time to write a formula | Quick |
You may also be interested in How to Display the Formula as Text in Excel?
Reverse Concatenate In Excel by Text to Columns
You may also be interested in How to Sum Intersections of Multiple Ranges (Excel)
Step 1: Select cells to be split
Step 2: Choose Data group
Step 3: Select Text to Columns
Step 4: Press “Next”
Step 5: Check the “Space” box
Step 6: Press “Finish”
End Result (Option 1)
You may also be interested in How To Change The Default Number Of Sheets In Excel?
Reverse Concatenate In Excel by Formula
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",50)),(COLUMNS($A:A)-1)*50+1,50))How to customize this formula?
First, replace the ” ” with your own delimiter.
If your delimiter is a comma, then your formula should look like this.
=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",50)),(COLUMNS($A:A)-1)*50+1,50))Second, replace $A1 with your own cell
If the cell you are trying to unconcatenate is Cell C23, then your formula should look like this.
=TRIM(MID(SUBSTITUTE($C23,",",REPT(" ",50)),(COLUMNS($A:A)-1)*50+1,50))You may also be interested in Look Up the Last Value in Column/Row in Excel
How does the formula work?
REPT(" ",50)This part repeats the ” ” (space) for 50 times.
The function saves you time from repeating the space manually.
SUBSTITUTE($A1," ",REPT(" ",50))This part substitute the space with 50 spaces.
It turns ” ” into ” “.
With this part of the formula, “I Have A Pen” turned into “I Have A Pen”
(COLUMNS($A:A)-1)*50+1
This part of the formula helps identify from which character we are going to extract the strings.
MID(SUBSTITUTE($A1," ",REPT(" ",50)),(COLUMNS($A:A)-1)*50+1,50)This part extract the middle part of the strings.
=TRIM(MID(SUBSTITUTE($C23,",",REPT(" ",50)),(COLUMNS($A:A)-1)*50+1,50))This part removes the trailing spaces and any other extra spaces.
End Result (Option 2)
Reverse Concatenate In Excel by Flash Fill
What is Flash Fill?
Flash Fill is a cool feature that was released in Excel 2013. If you are using Excel 2013 or later version, you will be able to use this amazing feature.
Flash Fill allows you to accomplish many things at once. It can perform concatenate, cells splitting, replace.
You might also be interested in Edit The Same Cell In Multiple Excel Sheets
How to use Flash Fill?
Step 1: Manually input an model row
Step 2: Select Cell B2
Step 3: Select Data tab,
Step 4: Press “Flash Fill”
Step 5: Select Cell C2 and repeat step 3 and 4.
Step 6: Select Cell D2 and repeat step 3 and 4.
End Result (Option 3)
Yup you are correct. There is no result in Cell E2 and Cell E3.
This is because Flash Fill has its limitation. When the pattern is too complicated to recognize, Flash Fill fail to perform its function.
Don’t get me wrong. I am not saying you should avoid using Flash Fill.
Instead, I enjoy using Flash Fill for its simplicity.
But I really have to say that Flash Fill is not for every single situations.
In this article, I showed you how to do the converse of concatenate – split the content of one cell into individual cells.
Do you find this article helpful? Subscribe to our newsletter to get regular Excel tips and exclusive free Excel resources.
