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.
Read More: How to Convert Text to Columns in Excel
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.
Read More: How to Split Text to Columns Automatically with Formula in Excel
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.
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.
Related Articles
- How to Use Line Break as Delimiter in Excel Text to Columns
- How to Convert Column to Text with Delimiter in Excel
- How to Use Text to Columns in Excel for Date
- How to Convert Text to Columns Without Overwriting in Excel
- How to Convert Text to Columns with Multiple Delimiters in Excel
- How to Convert Text to Columns in Excel with Multiple Spaces
- How to Use Text to Columns Feature with Carriage Return in Excel
- [Fixed!] Excel Text to Columns Is Deleting Data
- Excel Text to Columns Not Working
