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

Get FREE Advanced Excel Exercises with Solutions!

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.

how to undo text to columns in excel


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)

how to undo text to columns in excel

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

how to undo text to columns in excel

  • 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)

Utilizing CONCATENATE Function to Undo Text to Columns in Excel

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

Utilizing CONCATENATE Function to Undo Text to Columns in Excel

  • 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.

Undo Text to Columns by Applying VBA Code in Excel

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

Undo Text to Columns by Applying VBA Code in Excel

  • 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

Undo Text to Columns by Applying VBA Code in Excel

  • 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.

How to Stop Text to Columns Splitting While Pasting in Excel

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

How to Stop Text to Columns Splitting While Pasting in Excel

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

How to Stop Text to Columns Splitting While Pasting in Excel

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo