How to Undo Text to Columns in Excel: 3 Simple Methods

Method 1 – Using Ampersand Operator

Steps:

• Double-click on cell D5 and type in the following formula:

• Press Enter to undo the text in cell D5.

• Copy the formula to the cells below to get the same result in these cells as well.

Method 2 – Utilizing CONCATENATE Function to Undo Text to Columns in Excel

Steps:

• Click cell D5 and enter the below formula:

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

• Drag the Fill Handle down to copy the formula to the cells below.
• The First and Last Names will be put back into column D.

Method 3 – Undo Text to Columns by Applying VBA Code in Excel

Steps:

• Go to the Developer tab and select Visual Basic.

• In the Visual Basic window, click on Insert and select Module.

• 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

• Close the VBA window and enter the following formula in cell D5:

• 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

Steps:

• Enter any data in cell B10. We entered ‘a‘ as an example.
• Go to the Data tab and to Data Tools.
• Under this section, select Text to Columns.

• In the new window, select Delimited and click Next.

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

• Open your dataset and copy them. We copied from Notepad for this example.

• Paste the data into Excel; it won’t 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 correctly if you are working with URLs.

Download Practice Workbook

You can download the practice workbook from here.

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 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
• [Fixed!] Excel Text to Columns Is Deleting Data
• Excel Text to Columns Not Working

<< Go Back to Splitting Text | Split in Excel | Learn Excel