How to undo text to columns in Excel.

Microsoft Excel is an effective spreadsheet program that enables users to efficiently organize, analyze, and manipulate data. The Text to Columns function, which divides cell contents into multiple columns based on a specified delimiter, is one of its useful features. To put the separated values back together into a single cell, you might occasionally need to undo or reverse the Text to Columns operation. We will investigate straightforward Excel techniques to accomplish this in this documentation.

We’ll use a dataset with two columns for this tutorial. The goal is to give a simple illustration that demonstrates each step required to undo the text to columns in Excel. Although the techniques used here are based on MS Excel 365, they ought to function similarly in earlier iterations.

Method 1 – Undoing Text to Columns using the Ampersand Operator

The Text to Columns feature in Excel can be helpful for dividing cell contents into multiple columns based on a delimiter when working with data. In some circumstances, though, you might need to perform the opposite operation and put the separated values back together in a single cell. 

Step 1 – Select the Cell you want to undo

  • Decide which cell you want to reverse the text to columns operation in.
  • To select the cell, click on it.

Step 2 – Enter the Formula

  • To combine two columns, enter the following formula:
    =(A2&” “&B2)
  • Where A2 and B2 are the desired columns in our specific case, you can replace them with your desired columns.
  • Hit that Enter Button.

Step 3 – Copy the formula to the remaining cells

  • Choose the cell where the formula was applied.
  • You can also copy the formula by pressing Ctrl+C, and copy and paste the formula into the cells below.

Method 2 – Utilizing CONCATENATE Function

Excel’s CONCATENATE function provides a potent method for stringing together the contents of multiple cells. Using this function offers a simple way to reverse the Text to Columns operation. You can quickly undo the text-to-columns operation and combine the desired columns back into one by entering the CONCATENATE formula.

Step 1 – Select the cell

  • Decide which cell you want to use to combine the columns in.
  • Click on the cell to select it.

Step 2 – Enter the Formula

  • Enter the following formula:
    =CONCATENATE(A2,” “,B2).
  • Where A2 and B2 are the two columns that need to be joined.
  • Press the Enter button to execute the formula.

Step 3 – Copy the Formula

  • Select the cell, in which the formula was entered.
  • To duplicate the formula in additional cells below, drag the Fill Handle downward.
  • By combining the relevant columns, you can extend the formula to get the same outcome.

Conclusion:

You can successfully undo Excel’s text to columns operation and merge the separated values back into a single column by following these easy steps. Excel’s versatility enables you to select either the CONCATENATE function or the Ampersand operator based on your preferences. Always remember to modify these techniques to fit your unique dataset and needs. Excel is a useful tool for analysing and manipulating data because of its versatility.