What is the Opposite of Concatenate in Excel?

In our earlier article, “Concatenate Strings in ExcelConcatenate Strings In ExcelConcatenation of Strings is accomplished using Excel’s two concatenate methods, the & operator and the concatenate inbuilt function. read more,” we have seen how to combine values of more than one cell into one with various practical examples. However, have you ever thought about what would be the complete opposite of concatenating in Excel?

Yes, we can do the opposite of concatenating in Excel. We have several methods to do this job. First, this article will show how to split one cell’s data into multiple cells.

How to do the Opposite of Concatenate in Excel?

Below are the examples of excel opposite to concatenate.

Method #1 – Split Values by Using Text Functions

Excel TEXT functions are popular ways to split one cell value into multiple cells. Excel’s LEFT function, RIGHT function, and MID function in excelMID Function In ExcelThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more are popular text functions. For example, if you have the full name of employees, we can split the names into first and last names.

We are using the below data for this example.

We have the full name of cricketers here. However, we need first to extract the first name here. We can do this by applying the LEFT function in ExcelLEFT Function In ExcelThe left function returns the number of characters from the start of the string. For example, if we use this function as =LEFT ( “ANAND”,2), the result will be AN.read more.

The LEFT function alone cannot do the job here because once the value to be split is selected; we must specify the number of characters we need to extract from the left side of the selected data. In this example, the first name characters of all the cricketers have a different number of characters, so we need to employ the FIND excel functionFIND Excel FunctionFind function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer.read more.

Like this, we can do the opposite of concatenating in Excel. We can also do the opposite of concatenating with another method, “Text to Column.”

  • For example, in the “Sachin Tendulkar,” space character position is 7, so 7 – 1 = 6. Therefore, 6 is the number of characters we need from the left side of the name “Sachin Tendulkar.” In the number of characters, the argument opens the FIND function. Find text is what we need to find. In this example, we need to find space characters, so we must supply space in double-quotes. Within text means in which text we need to find the space. So this is our full name, A2 cell. The last parameter is irrelevant so ignore that. So now, the FIND function will return the space character position as 7, but we do not need space characters, so we must apply -1 for the FIND function. Close the bracket and press the “Enter” key. We got the first name. Drag the formula to the rest of the cells. Now, we will see how to get a second or last name. But, first, we must open the RIGHT function in Excel. We cannot manually supply the number in the number of characters argument. Therefore, we also need to employ two other functions to get the number of characters.We need to use the FIND and LEN functions in Excel. The LEN function will return the number of characters in the selected value, and the FIND function will return the space character position. So, the LEN and FIND functions are the number of characters we need from the right-hand side. We got the last name. Drag the formula to other cells to get the result. For example, in the name “Virat Kohli,” the total number of characters, including zero, is 11, and the space character position is 5. So 11 – 5 = 6. So, the number of characters we need from the right side is 6, “Kohli.”

We cannot manually supply the number in the number of characters argument. Therefore, we also need to employ two other functions to get the number of characters.We need to use the FIND and LEN functions in Excel.

Method #2 – Opposite of Concatenate Through Text to Column

It is the best example of a simple procedure. In addition, this method does not involve any complex formulas. We will see this in the below example.

The above data should be in the below format.

It looks like one hell of a task.

We must select the data first.

Then, go to Data > Text to Columns in excelText To Column In ExcelText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more. Else, we can press the excel shortcut keyPress The Excel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more ALT + A + E.

We can see the below window when we click on “Text to Columns.”

Now in the next window, we need to select the Delimiter,  the common thing, combining all the columns into one. In this case, “Comma,” so select comma.

Click on “Next” and click on “Finish” in the next window. We will get the opposite of the concatenate.

Like this, we can use functions and “Text to Columns” in Excel to do the opposite of concatenating.

This article is a guide to the Opposite of Concatenate in Excel. We discuss the top 2 methods to opposite concatenate 1) Text functions and 2) Text to Columns method in Excel with examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel AVERAGEIFS FunctionExcel AVERAGEIFS FunctionWhen there are multiple conditions to be met, the AVERGAEIFS function in Excel is used to find the average from the target range of cells. We used AVERGAEIF for a single condition, but AVERGAEIFS for multiple conditions.read moreCount Characters in Excel CellSubstitute Formula In ExcelCombine Cells in Excel