FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Opposite Of Concatenate In Excel

Updated on December 31, 2023
Article byJeevan A Y
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

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.

For example, consider the below example showing a name in column A. Let us learn how to split the name into other cells.

Opposite of Concatenate Intro

The steps are:

Step 1: First, select the cell which we want to split.

Step 2: Next, go to Data – Text to Columns in Excel.

Step 3: Now, the window, Convert Text to Columns Wizard – Step 1 of 3 appears.

Step 4: Next, select Delimited in Convert Text to Columns Wizard – Step 2 of 3.

Step 5: Then, select Space in Convert Text to Columns Wizard – Step 3 of 3 since the first and last names are separated by a space.

Step 6: Now, click on Finish.

Opposite of Concatenate Intro - Output.jpg

Likewise, we can split data into other cells.

In this article will show how to split one cell’s data into multiple cells.

Key Takeaways

  • Opposite of concatenate in Excel refers to the method to split data from one cell into other cells.
  • To split data from one cell to others, we can use Excel TEXT functions, such as RIGHT, LEFT functions.
  • Alternatively, we can use Text to Columns in Excel method to split data into other cells.
  • While using text to columns in Excel method, we can split content on the basis of tab, semicolon, comma, and space.
  • If there is any other symbol used to divide text, we can use the Other: option to specify.
 

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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 excel are popular text functions.

We are using the below data for this example.

Opposite to Concatenate Example 1

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 Excel and FIND excel function 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.

Using the FIND function, we can find the space character position in the selected value.

  1. 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.”


    Opposite to Concatenate Example 1-1

  2. Next, in the number of characters, the argument opens the FIND function.


    Opposite to Concatenate Example 1-2

  3. 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.


    Opposite to Concatenate Example 1-3

  4. Now, within text means in which text we need to find the space. So this is our full name, A2 cell.

  5. 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.


    Opposite to Concatenate Example 1-5

  6. Next, close the bracket and press the Enter key. We got the first name.


    Opposite to Concatenate Example 1-6

  7. Now, drag the formula to the rest of the cells.


    Opposite to Concatenate Example 1-7

  8. Now, we will see how to get a second or last name. But, first, we must open the RIGHT function in Excel.


    Opposite to Concatenate Example 1-8
    Remember, 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.

    Therefore, we need to use the FIND and LEN functions in Excel.

  9. Remember, 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.


    Opposite to Concatenate Example 1-9

  10. Now, we got the last name.


    Opposite to Concatenate Example 1-10

  11. Drag the formula to other cells to get the result.


    Opposite to Concatenate Example 1-11

  12. 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.”


    Opposite to Concatenate Example 1-12

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.”

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.

Opposite to Concatenate Example 2

The above data should be in the below format.

Opposite to Concatenate Example 2-6

It looks like one hell of a task.

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 combining different columns into one is a comma (,).

To begin with, we must select the data.

Opposite to Concatenate Example 2-1

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.

Shortcut key for text to Column
Example 2-2

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

Example 2-3

Now, select “Delimited,” and then, click on “Next.”

Example 2-4

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

Example 2-5

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

Example 2-6

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

Examples

Example #1

For example, consider the below example showing an employee’s name, age and country in column A. Let us learn how to split the name into other cells.

Opposite of Concatenate - Example 1.jpg

The steps are:

Step 1: First, select the cell which we want to split.

Step 2: Next, go to Data – Text to Columns in Excel.

Opposite of Concatenate - Example 1 - Step 1.jpg

Step 3: Now, the window, Convert Text to Columns Wizard – Step 1 of 3 appears.

Step 4: Next, select Delimited in Convert Text to Columns Wizard – Step 2 of 3.

Opposite of Concatenate - Example 1 - Step 3.jpg

Step 5: Then, select Space in Convert Text to Columns Wizard – Step 3 of 3 since the first and last names are separated by a comma.

Opposite of Concatenate - Example 1 - Step 5.jpg

Step 6: Now, click on Finish.

Opposite of Concatenate - Example 1 - Step 6.jpg

Likewise, we can split data into other cells.

Example #2

Consider the below table showing list of Phd scholars in an university. Now, let us learn how to split the text into First Name and Last Name using TEXT functions in Excel.

Opposite of Concatenate - Example 2.jpg

The steps are:

First, we need to extract the first name in cell B2. We can do this by applying the LEFT function in Excel and FIND excel function.

So, insert the formula, =LEFT(A2,FIND(“ “A2)-1)

 Then, press Enter key.

Opposite of Concatenate - Example 2 - Left formula.jpg

Finally, we can see the result in cell B2.

Now, using AutoFill option, we can obtain the results in other cells.

Opposite of Concatenate - Example 2 - Autofill.jpg

Next, let us extract the last name in cell C2.

Then, insert the formula, =RIGHT(A2,LEN(A2)-FIND(” “,A2)) in cell C2.

Next, press Enter key. We can see the result in cell C2.

Opposite of Concatenate - Example 2 - Right Formula.jpg

Next, using AutoFill option, we can obtain the results in other cells.

Opposite of Concatenate - Example 2 - Right - Autofill.jpg

Like this, we can do the opposite of concatenating in Excel.

Example #3

For example, consider the below example showing product, price and stock availability in column A. Let us learn how to split the text into other cells.

Example 3.jpg

The steps are:

Step 1: First, select the cell which we want to split.

Step 2: Next, go to Data – Text to Columns in Excel.

Step 3: Now, the window, Convert Text to Columns Wizard – Step 1 of 3 appears.

Step 4: Next, select Delimited in Convert Text to Columns Wizard – Step 2 of 3.

Step 5: Then, select Space in Convert Text to Columns Wizard – Step 3 of 3 since the first and last names are separated by a comma.

Step 6: Now, click on Finish.

Example 3 - Step 6.jpg

Likewise, we can split data into other cells.

Important Things To Note

  • To split text into columns, we need to select Data tab.
  • Then, click on Text to Columns in Excel option.
  • Select Delimited and click on the desired delimiters.

Frequently Asked Questions

1. What is the opposite of concatenate in Excel?

Excel’s concatenate function enables users to combine the data. In simple terms, the opposite of concatenate function helps users to split text from one cell to multiple cells.

2. Explain how to split data into other cells with an example.

Consider the below example showing a student’s name, marks and exam status in column A. Let us learn how to split the data into other cells.

FAQ 2.jpg

The steps are:
Step 1: First, select the cell which we want to split.
Step 2: Next, go to Data Text to Columns in Excel.
Step 3: The window, Convert Text to Columns Wizard – Step 1 of 3 appears.
Step 4: Next, select Delimited in Convert Text to Columns Wizard – Step 2 of 3. Press Next.
Step 5: Then, select Space in Convert Text to Columns Wizard – Step 3 of 3 since the first and last names are separated by a comma.
Step 6: Now, click on Finish.

FAQ 2 - Output.jpg

3. What are the two methods to split data into other cells?

There are two simple methods with which we can separate or split text into multiple cells. They are:

• Using Excel Text functions like RIGHT function, LEFT function.
• Using Text to Columns in Excel option under Data tab in Excel.

Recommended Articles

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: –

Download Template

This article must be helpful to understand the Excel Column Auto Width, with its formula and examples. You can download the template here to use it instantly.