When you’re cleaning up your Excel data such as concatenating two columns, removing spaces from cells or otherwise transforming data using formulas or functions, you typically end up with two columns: one containing the original values and other containing the formulas used to transform the data. Once you’re happy with the results of your formulas, you can then replace the original data with the transformed data.
To replace original values with transformed values:
- In a new column, enter the formulas that you want to use to transform your data.
- Once you’re happy with the results, select all of the cells containing the formula.
- Press Ctrl + C to copy the data to the clipboard.
- Select the cells containing the original data.
- Click the Home tab on the Ribbon.
- Click the arrow on the Paste button and click Paste Special.
- In the Paste Special dialog box, click the radio button next to Values.
- Click OK.
- You can now delete the column containing the formulas.