What Is Opposite Of Concatenate In Excel?
The opposite of concatenate in Excel is a technique that enables one to split the data of a cell and display it in two or more cells.
Users can perform the opposite of concatenation in Excel for improved data management and evaluation and avoid data overfitting. The functionality also benefits users when creating models in the Data Science domain.
For instance, the image below shows US states and their capitals in column A.
The aim is to split the given data to show the US states in column A and their capitals in column B.
We can achieve the desired output using opposite of concatenate in Excel option, the Text to Columns Excel feature.
We select the range A1:A6 and choose the Text to Columns option in the Data tab.
Step 1 in the Convert Text to Columns Wizard window appears to help us perform the text to columns action in steps.
In step 1, choose the Delimited option and click Next. In step 2, choose Other in the Delimiters section and enter ‘–‘ in the field against Other.
Next, click Finish to view the data of column A cells split into columns A and B cells. Finally, we can format column B as column A to make the final dataset more presentable.
Thus, using opposite of concatenate in Excel option, the Text to Columns helps split the given data at every instance of the chosen delimiter in the data. For example, in this case, the ‘–’ appears once, leading the feature to break the data into two parts, the US state and capital, which are before and after the delimiter.
Table of contents
Key Takeaways
- The opposite of concatenate in Excel enables us to split merged data in a cell into multiple cells in contiguous and non-adjacent columns.
- Users can perform the reverse of concatenation in Excel to extract specific sections from the given data and split a complex dataset into two or more cells for better analysis.
- We can use Text functions, such as LEFT, RIGHT, MID, LEN, SEARCH, and FIND, or the Text to Columns feature to perform the reverse of concatenate in Excel.
- We can use Text functions-based VBA codes to perform the required reverse of concatenate in Excel.
How To Do The Opposite Of Concatenate In Excel?
We can perform the opposite of concatenate in Excel using the following methods:
- Split Values By Using Text Functions
- Opposite Of Concatenate Through Text To Column
Let us see the two methods in detail to use them when we need opposite of concatenate in Excel.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
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.
Method #1 – Split Values By Using Text Functions
The method to split values using Text functions is as follows:
- Select a target cell to display the required section of the given data.
- Enter the appropriate Text functions-based opposite of concatenate in Excel formula. Typically, the most widely used Text Excel functions are LEFT, RIGHT, MID, and LEN.
- Press Enter to view the required output.
Likewise, we can iterate steps 1 to 3 to display different parts of the given data in multiple cells. Thus, the final output will appear as it will be when we split the entire data into multiple cells.
Method #2 – Opposite Of Concatenate Through Text To Column
The process to achieve the opposite of concatenation through text to the column is as follows:
- Choose the cell or range containing the data we aim to split into two or more adjacent columns’ cells.
- Choose the Data tab → Text to Columns feature.
- The Convert Text to Columns Wizard appears to implement the text to columns action in two to three steps.
[ Alternatively, press Alt + A + E to choose the feature and open the wizard window.]
- In the first step, choose the applicable file type that best describes the given data, Delimited or Fixed width.
We can select Delimited when a delimiter separates the different fields in the concatenated data in excel. On the other hand, assume the fields in the merged data are aligned in columns, with each field separated by spaces. Then, we must select the Fixed width option.
If we choose Delimited and click Next, the second step will require us to choose the appropriate delimiters. On the other hand, if we choose Fixed width and click Next, the second step gives us the option to create, delete, and move break lines to split the given data.
Furthermore, in both scenarios, we can view the data preview to ensure the split data meets our requirements.
- If we must format the split data into a specific format, select Next. The third step will enable us to choose the required format. Also, we can click the Advanced option to open the Advanced Text Import Settings window to apply advanced numeric data formatting. Next, choose the required destination cell address where we aim to apply the chosen formatting settings. Click Finish to complete the data splitting into multiple cells action.
- If we do not need to format the split data, click Finish after choosing the required options in step 2 to complete the data splitting into multiple cells action.
Examples
The following examples will help us understand how to use the Text functions or the Text to Columns feature when we need opposite of concatenate in Excel.
Example #1
The dataset below contains employee names in column A, with the names having a first, middle, and last name.
The task is to split the employee names in column A cells into column B:D cells based on their first, middle, and last names.
We can apply the appropriate Text functions-based opposite of concatenate in Excel formula in each column B:D cells to achieve the required output.
- Step 1: Choose cell B2, enter the Text functions-based formula, and press Enter.
=LEFT(A2,FIND(” “,A2)-1)
First, the Excel FIND function finds the Space character position in the employee name Dorothy Curtisa Morris, which is 8. Next, the formula deducts 1 from 8, which results in the value 7.
The value of 7 will be the LEFT()’s second argument value, num_chars, with the first argument value being the specific employee name.
Thus, the LEFT() returns 7 characters in the employee name Dorothy Curtisa Morris from the left, Dorothy, which is the specific employee’s first name.
- Step 2: Choose cell C2, enter the Text functions-based formula, and press Enter.
=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1)-1)
The Excel SEARCH function finds the position of the first appearance of the Space character in the employee name Dorothy Curtisa Morris, which is 8. Next, the nested SEARCH() identifies the position of the second appearance of the Space character in the employee name Dorothy Curtisa Morris, which is 16.
Thus, the SEARCH functions help determine the MID()’s second and third arguments, start_num and num_chars, with the specific employee name being its first argument, text. So, the formula deduces the start_num and num_chars argument values to 9 and 7.
Hence, the MID() returns seven characters from the 9th position in the employee name Dorothy Curtisa Morris, Curtisa, which is the specific employee’s middle name.
- Step 3: Choose cell D2, enter the following Text functions-based formula, and press Enter.
=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2)+1)))
First, the LEN() returns the number of characters in the employee name Dorothy Curtisa Morris, 22. Next, the nested SEARCH() returns the position of the second appearance of the Space character in the employee name Dorothy Curtisa Morris, 16.
Next, the formula finds the difference between the values 22 and 16 to return the value of 6.
So, the RIGHT() accepts the value of 6 as the second argument value, num_chars, with the specific employee name being its first argument, text. Thus, the RIGHT() returns sixcharacters from the right end of the employee name Dorothy Curtisa Morris, Morris, which is the specific employee’s last name.
- Step 4: Select cells B2:D2.
Next, use the Excel fill handle to implement the formulas in the range B3:D7.
Example #2
The dataset below contains student names, the subjects they topped and their scores.
However, the data is in one column, column A.
So, we can split the data into cells in three adjacent columns using the Text to Columns feature, as explained below:
- Step 1: Choose the range A1:A10 and select Data → Text to Columns option.
- Step 2: Step 1 in the Convert Text to Columns Wizard appears, where we must choose the Delimited option and click Next.
- Step 3: Since commas separate the values in each cell in the source dataset, choose Comma in the Delimiters section in step 2.
Next, click Finish to close the wizard and view the source data split into cells in three adjacent columns, A:C.
- Step 4: Format columns B:C as column A and adjust the columns’ widths to make the resulting dataset more presentable and clearer.
Thus, the Text to Columns feature splits the data in the chosen range at every occurrence of the cited delimiter into the cells in the adjacent columns.
Example #3
The dataset shown below contains the order delivery details of a list of items in one column, column A.
The requirement is to split the source data into four adjacent columns’, A:D, cells.
Then, here are the steps to use the Text to Columns feature to achieve the desired output.
- Step 1: Choose the range A1:A11 and select Data → Text to Columns option.
- Step 2: Step 1 in the Convert Text to Columns Wizard will open, where we must choose the Fixed width option and click Next.
Step 2 in the wizard shows the break lines in the data preview, where the data will split in the final output.
- Step 3: Create, delete, and move the break lines to the required positions in the source dataset’s preview according to the requirements. Follow the instructions to manage the break lines, which are provided in the wizard’s step 2.
Next, click Finish to close the wizard and view the source dataset split into the adjacent columns’ cells A:D.
- Step 4: Format columns B:D as column A and adjust the columns’ widths to make the resulting dataset clearer.
Important Things To Note
- Ensure to use the appropriate Text functions-based formula to perform the opposite of concatenate in Excel in the target cell correctly. Otherwise, the formula may split the given data incorrectly or return an error value.
- Ensure to choose the correct delimiter in the Convert Text to Columns Wizard so that the Text to Columns feature splits the given data at the required positions.
- If the chosen file type in the Convert Text to Columns Wizard is Fixed width, ensure to position the break lines in the source data preview correctly. Otherwise, the data will split at the undesired places, leading to the unmerged data being incorrect.
Frequently Asked Questions (FAQs)
The opposite of CONCATENATE in Excel VBA is as follows, explained with an example.
The dataset below lists books and their authors in column A.
The aim is to split the data to show the book names in column B and the authors in column C.
Then, we must perform the opposite of the CONCATENATE function, and we shall see how to do it in Excel VBA.
• Step 1: The sheet containing the source dataset should be the active worksheet. Next, use Alt + F11 to open the VBA Editor.
• Step 2: Select the applicable VBAProject and choose Module in the Insert tab in the top menu.
A module window will appear.
• Step 3: Type the code to split the source data into columns B and C cells.
• Step 4: Select the play icon in the menu to execute the VBA code.
Finally, open the active worksheet to view the source data split into columns B and C cells according to our requirements.
The VBA code uses the Text functions to split and show the left and right sections of each column A cell data (before and after ‘:’) in the corresponding columns B and C cells.
The benefits of reversing the CONCATENATE function in Excel are as follows:
• The process helps organize and manage data with more ease.
• The method helps extract the required sections of information from the given data.
• The method makes data analysis more straightforward and ensures we avoid errors that may arise due to merged data.
You can reverse a date CONCATENATE in Excel using the Text to Columns feature, as explained below:
1) Choose the cell or range containing date-concatenated data you aim to split into multiple cells.
2) Select the Data tab → Text to Columns option.
3) Step 1 in the Convert Text to Columns Wizard window will appear. Choose the required file type, Delimited or Fixed width. Click Next.
4) If the chosen option in the previous step is Delimited, select the appropriate delimiter in the wizard’s step 2. On the other hand, if the chosen option is Fixed width, set the required break lines at the required positions in the source dataset preview in the wizard’s step 2. Click Next.
5) The wizard’s step 3 shows the data preview post the data split into cells in adjacent columns. So, choose the column containing date values in the preview. Next, select Date in the Column data format section and the required date format from the field against the Date option.
6) Click Finish to complete the reverse date CONCATENATE in Excel action.
Download Template
This article must be helpful to understand the Opposite Of Concatenate In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Opposite Of Concatenate In Excel. We learn how to split values using Text functions & Text to Columns option, with examples. You can learn more from the following articles –
Leave a Reply