Opposite Of Concatenate In Excel

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.