Opposite of Concatenate in Excel

What is 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. Have you ever thought about what would be the complete opposite of concatenate in excel?

Yes, we could do the opposite of concatenate in excel. We have several methods to do this job, and in this article, we will take you through the process of splitting one cell 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

One of the popular ways of splitting one cell value into multiple cells is using excel text functions. Popular text functions are “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.” For example, if you have the full name of employees, then we can split the names into first name and last name.

I am using the below data for this example.

We have the full name of cricketers here. We need to first extract the first name here; this can be done 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.

LEFT function alone cannot do the job here because once the value to be split is selected, we need to specify the number of characters we need to extract from the left side of 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.

So, like this, we can do the opposite of concatenate in excel. We can do the opposite of concatenating with another method as well, i.e., “Text to Column.”

Method #2 – Opposite of Concatenate Through Text to Column

This is the best example of a simple procedure. This method doesn’t involve any kind of complex formulas. We will see this in the below example.

The above data should be in the below format.

This looks like one hell of a task, isn’t it???

But actual work is easier than we assume. By using the one common thing which is combining this data, we can split accordingly. In this case, the common thing which is combining different columns into one is a comma (,).

Select the data first.

Go to Data > Text to Column 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, or else you can press the excel shortcut key ALT + A + E.

We will see below the window when we click on Text to Column.

By selecting “Delimited,” click on “Next.”

Now in the next window, we need to select the Delimiter, i.e., the common thing, which is 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.

So, like this, we can use functions and Text to Column in excel to do the opposite of concatenate.

Recommended Articles

This has been a guide to Opposite of Concatenate in Excel. Here we discuss the top 2 methods to reverse concatenate 1) Text Functions and 2) Text to Column method in Excel with examples and downloadable excel template. You may learn more about excel from the following articles –

• Excel AVERAGEIFS Function
• Count Characters in Excel Cell
• Substitute Formula In Excel
• Combine Cells in Excel