How to Undo Text to Columns in Excel (3 Simple Methods)

In this tutorial, I am going to share with you 3 simple methods on how to undo text to columns in Excel. Sometimes, you may not want to split your dataset into columns which can make it difficult to understand them. In the following section, I will show some quick and easy methods which will help you a lot in the above situations.

Text to Columns Feature in Excel

The text-to-columns feature in Excel is very powerful if you want to separate your dataset into different columns. This can also help to move text from one column to the other. This feature becomes very useful when you change your mind regarding how your data should be organized.

Undo Text to Columns in Excel: 3 Simple Methods

The dataset for this tutorial consists of 2 columns. We will use the following dataset to undo text to columns in Excel. This is mainly to keep things simple and clearly explain the steps. Although I am using MS Excel 365, the methods should work the same in the previous versions as well.

1. Using Ampersand Operator

The Ampersand operator in Excel works to join several texts as a single string. This feature is very helpful to undo text to columns in Excel. Let us see how to apply this.

Steps:

First, double-click on cell D5 and type in the following formula:

=(B5&” “%C5)

Now, press Enter and this will undo the text to columns in cell D5.

Finally, copy the formula to the cells below to get the same result in these cells as well.

2. Utilizing CONCATENATE Function to Undo Text to Columns in Excel

We can use the CONCATENATE function in Excel easily to undo text to columns in Excel. This function can join up to 30 values as a single text string. Let us see how to use it.

Steps:

To begin with, click on cell D5 and enter the below formula:

=CONCATENATE(B5,” “,C5)

Next, press the Enter key, and Excel will rejoin the data in columns A and B in cell D5.

Then, drag the Fill Handle down to copy the formula to the cells below.
As a result, the First and Last Names will be put back into column D.

3. Undo Text to Columns by Applying VBA Code in Excel

If you need to undo text to columns in many Excel worksheets, then VBA is the best option. We can write just a few lines of VBA code and this will do the job within seconds. Follow the steps below to do this.

Steps:

First, go to the Developer tab and select Visual Basic.

Next, in the Visual Basic window, click on Insert and then select Module.

Now, in the new Module1 window, enter the following code:

Public Function UndoTextToColumns(Ran As Range, Optional D As String = " ") As String
    Dim undoTxt
    undoTxt = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(Ran.Value))
    UndoTextToColumns = Join(undoTxt, D)
End Function

After that, close the VBA window and enter the following formula in cell D5:

=UndoTextToColumns(B5:C5,” “)

Finally, press the Enter key and copy the formula to the rest of the cells below.

How to Stop Text to Columns Splitting While Pasting in Excel

Sometimes you might copy and paste data into Excel and the Text to Columns feature can automatically split them. This can create unwanted problems. So we will see below how to avoid this problem.

Steps:

First, enter any data in cell B10. I have entered ‘a‘ as an example.
Then, go to the Data tab and then to Data Tools.
Under this section, select Text to Columns.

Next, in the new window, select Delimited and click on Next.

Here, under the Delimiters section, uncheck all the options and click Finish.

Then, open your dataset and copy them. I have copied my data from Notepad for this example.

Finally, paste the data into Excel and now it will not automatically split the text into columns.

Things to Remember

You can use the keyboard shortcut Alt+A+E to bring Text to Columns.
Remember that the result from the Text to Columns feature is static.
The above methods might not work properly if you are working with URLs.

Download Practice Workbook

You can download the practice workbook from here.

Undo Text to Columns.xlsm

Conclusion

I hope that you understood the methods I showed in this tutorial on how to undo text to columns in Excel. Although we have used a dataset with only 2 columns, you can apply these methods to any large dataset as well. If you get stuck in any steps, I would suggest going over them a few times. If you have any queries, please let me know in the comments.