As an Excel user, you must be familiar with CONCATENATE function. It helps in merging more than one string in a single cell. At times, you often need to perform a function that exactly does the opposite of CONCATENATE function. You may need to split the merged text into multiple columns. Do you know how would you do it?
In such situations, you must be thinking about what is the opposite of CONCATENATE in Excel. In this post, you will find different solutions for such cases. Without further ado, let’s dive in to see how it happens:
How to Perform the Opposite of CONCATENATE in Excel with a Formula?
In this method, you will see the opposite of concatenate function by using a formula. Let’s see how below-given steps can help you:
- Choose a cell in which you need the result.
- Open the Formula bar and enter the following formula:
=TRIM(MID(SUBSTITUTE($A2,”,”,REPT(“ “,999))),COLUMNS($A:A)*999-998,999))
- In this formula, A2 is the cell in which you need to divide the text.
- Press the ENTER key.
- Select and drag the cell in which you have entered the formula to the right side. You will find other values in their cells.
That’s it!
All the split text from one cell into multiple cells will appear in front of you.
Notice: This method works only when your data is separated with the help of commas. Otherwise, you may get errors.
How to Perform the Opposite of CONCATENATE in Excel Through Text to Column?
In this method, you will not find any tricky formulas to be used because this is a straightforward approach you can choose from all the methods. Let’s find out how this procedure works:
- You need to use this data in the format given below:
- Don’t worry about the format, as I said this procedure is easy, and you will feel it.
- You can split data easily by using a common thing that combines the data. Well, that common thing is nothing else but a comma (,).
- First of all, select the data.
- Open the Data tab and choose Text to Columns in Excel or else you can even use this shortcut command:
ALT + A + E
- Click on “Text to Columns” and a window will appear.
- Choose the Delimited option and click on Next.
- Choose the Delimiter option from the next window that pops up. “Comma” is the main thing that you need to select.
- Select “Next” and then press the Finish button. You will instantly see the opposite of the concatenate.
Similarly, you can use this function through text to columns in Excel.
How to Use Text Functions to Split into Multiple Cells to Perform Opposite of Concatenate?
In this method, we have a dataset in which you can see random names given in Column B. Here you need to split the names while showing them separately in the next two cells.
- Choose the first cell C5 and enter the following formula:
=LEFT(B5,FIND(“ “,B5)-1)
- Press the ENTER key to add and drag the Fill Handle to AutoFill the cells given in Column C.
- You will now see all the first names slit from the full names given in Column B.
Let’s see how this formula works:
In this formula, you can see the FIND function in Cell B5 that gives the position of the space character. From the left side, the LEFT function finds the name and gives the number of characters you set earlier by using the FIND function.
Let’s see how you can extract the second name:
- Add the formula in Cell D5:
=RIGHT(B5,LEN(B5)-FIND(“ “,B5))
- Press the ENTER key and then AutoFill column D. All the last names will appear under the Part 2 header.
Let’s see how this formula works:
- Using the LEN function, you will get the net characters given in Cell B5, which is 15.
- With the FIND function, you will get the position of the space given in the text gives 8.
- With the RIGHT function, you get 15-8=7 characters from the right.
How to Perform Opposite of CONCATENATE with the Flash Fill Option in Excel?
In this method, you can split the merged data by using the Flash Fill option.
- Select a cell in which you need the split text.
- Select and drag from the bottom right side of the cell until you find all the other cells have their values.
- By simply repeating the process, you can apply this function to each row.
That’s it!
Isn’t that simple?
Notice: This method works only for those lists that Excel extends on its own.
Let’s Wrap Up:
In this post, you have learned what is the opposite of concatenating in Excel by using multiple methods. Each method contains highly effective tricks that help you in finding a solution. Keep exploring Excel and continue spreading the knowledge.
