WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Opposite of Concatenate in Excel

Opposite of Concatenate in Excel

What is Opposite of Concatenate in Excel?

In our earlier article “Concatenate Strings in Excel,” 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.

You can download this Opposite of Concatenate Excel Template here – Opposite of Concatenate Excel Template

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

Opposite to Concatenate Example 1

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

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

Using Find, we can find the space character position in the selected value. For example, in the name “Sachin Tendulkar,” space character position is 7, so 7 – 1 = 6; this is the number of characters we need from the left side of the name “Sachin Tendulkar.”

Opposite to Concatenate Example 1-1

In the number of characters, the argument opens the FIND function.

Opposite to Concatenate Example 1-2

Find Text is what we need to find; in this example, we need to find space character, so supply space in double-quotes.

Opposite to Concatenate Example 1-3

Within Text means in which text we need to find the space, so this is our full name, i.e., A2 cell.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Opposite to Concatenate Example 1-4

The last parameter is irrelevant so ignore that. So now, the FIND function will return space character position as 7, but we don’t need space character, so apply -1 for FIND function.

Opposite to Concatenate Example 1-5

Close the bracket and hit the enter key. Wow!! We got the first name.

Opposite to Concatenate Example 1-6

Drag the formula to the rest of the cells.

Opposite to Concatenate Example 1-7

Now we will see how to get a second or last name. Open RIGHT function in excel.

Opposite to Concatenate Example 1-8

In the number of characters argument, we cannot manually supply the number. Even here also we need to employ two other functions to get the number of characters.

Here we need to use FIND & LEN function in Excel.

LEN function will return the total number of characters in the selected value, and FIND will return the space character position. So, LEN – FIND is the number of characters we need from the right-hand side.

Opposite to Concatenate Example 1-9

We got the last name.

Opposite to Concatenate Example 1-10

Drag the formula to other cells to get the result.

Example 1-11

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 total number of characters we need from the right side is 6, i.e., “Kohli.”

Opposite to Concatenate Example 1-12

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.

Opposite to Concatenate Example 2

The above data should be in the below format.

Opposite to Concatenate Example 2-6

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.

Opposite to Concatenate Example 2-1

Go to Data > Text to Column in excel, or else you can press the excel shortcut key ALT + A + E.

Shortcut key for text to Column

Example 2-2

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

Example 2-3

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

Example 2-4

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.

Example 2-5

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

Example 2-6

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Opposite of Concatenate Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More