Opposite of Concatenate in Excel (Table of Contents)
Introduction to Opposite of Concatenate in Excel
As we all know, the Excel Concatenate function can be used to combine multiple strings in one single cell. However, some situations arise where you might be in need to do exactly the opposite of concatenating, i.e. split the combined text into different columns. In such situations, what we can do? We have solutions for such cases. We can use Excel’s Text to column feature, Excel’s Flash Fill option. We can also use some custom formulae to split the concatenated text. Let’s see the methods one by one.
How to Use Opposite of Concatenate in Excel?
Excel Concatenate function is very simple and easy. Let’s understand how to use this function in Excel with some examples.
Example #1 – Split cells using Text to Column
We can split texts with different methods and different formulae. However, one of the simplest, best and time-saving methods in Excel’s Text to column facility is to split the text into different columns. Suppose we have data as below, and we wanted the information of Employees Name and Age in two different columns named B and C.
- In Excel, Go to Data in the uppermost pane and select Text to Column wizard.
- Once you click Text to Column wizard, a new window will pop up naming Convert Text to Columns Wizard. Here select the Delimited option to separate the text. Click Next.
- Click Next> and you’ll be able to see a preview of the split text. Excel takes a guess where the split should be in data.
In this case, Excel guessed that the spilled should be happening wherever there is a space. Therefore it checked the box of Space under Delimiters and shown Data Preview as in the image above.
- Click Next> You’ll be popped up to the screen where you can now select the Data Format for each column as well as can set Destination for the split to be done at.
You can change the format of each split preview by simply clicking on the radio button next to the different Column data formats. Also, keep in mind that the Destination is set by default to the cell in which your original data is present. You are recommended to change the destination as keeping it the same will result in losing the original data.
Click on the Finish button. You will see an output as below.
In this way, Text to Column can be used as a reverse of the Concatenate function.
Example #2 – Splitting Text using Flash Fill Option in Excel
If you are using Excel version 2013 or above, one tool is really very handy for you in splitting text into different columns. It is called the Flash Fill feature. This tool not only populates the data into other cells automatically but also splits the cell data into different columns. Suppose we have data as shown below. What we want is First Name, Last Name, and Age in three different columns.
- Insert three new columns after the column containing data, namely First Name, Last Name, Age.
- Under the Column First Name, type the first name as “Ram”.
As soon as you type the name “Rogger” in the second cell of column “First Name”, Excel recognizes the pattern and fills all the next cells with the First name. You can see the output below.
- Press the Enter Key.
- Do the same procedure for Last Name and Age columns, and your final output would be like the image shown below.
Example #3 – Splitting Text using Formula
Suppose we have the same data as we had in the previous three examples. We can use the formulae to do the splitting of these text values.
In cell B2, put the LEFT Formula.
Drag the same formula in column B.
Here in this formula, the LEFT function helps you to extract data from the left. SEARCH function allows you to search the first space (“ “) from the left-hand side and returns the text in cell B2.
- In cell C2, put the formula as follows and press the Enter Key.
- Drag the same formula in column C.
Here, in this formula, the MID function helps you find the middle value from the text string. The MID function requires three arguments.
- Text within which you want to find the middle value (A2)
- Starting position which specifies from where the mid function starts extracting the string (SEARCH(” “, A2) + 1)
- Number of characters which specifies how many characters you wanted to extract from middle (SEARCH(” “,A2,SEARCH(” “,A2)+1) – SEARCH(” “,A2) – 1).
In cell D2, put the RIGHT formula and Press Enter Key.
Drag the same formula in column D.
Here in this formula, the Excel RIGHT function allows you to extract the last two characters from the given string.
- There is one more formula in which we can do all these calculations in a single shot and save your time. Let’s put the following formula in cell F2 of the current working sheet and see the magic:
=TRIM(MID(SUBSTITUTE($A2,” “,REPT(” “,999)),COLUMNS($A:A)*999-998,999))
Here in this formula, the only thing that will change is the delimiter value for different cases. Like comma or # in different cases are used, then replace space by these characters. Drag the formula in rows, and you will see the output as below:
Things to Remember About the Opposite of Concatenate in Excel
- While working with Text to the column, you have to make sure that you are selecting the option of “Treat consecutive delimiters as one”. For example, if you have a name in the format Ram, Prakash, as you can see, there are two delimiters (comma and space); this option will consider comma and space as a single delimiter and will separate the First and Last Name in two different columns. If you do not check this option, it will split the data into three different columns with First and Last Names in extreme columns and space as a separate string in one column.
- While working with text to the column, make sure you are selecting the output range properly. By default, the actual cell containing data is being selected. If you do not change that, it will result in replacing the original data.
- You can’t give output range from other worksheets while working with text to column method for separating text.
This is a guide to Opposite of Concatenate in Excel. Here we discuss How to use the Opposite of Concatenate in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –