How to Convert Merged Excel Text to Permanent Values Before Deleting Columns
Question details
The user needs to combine text from two columns into a single column, convert the result into permanent text, and safely delete the original columns without breaking the data.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and organizing spreadsheet data by merging information from multiple source columns and removing redundancies.
- Observed behavior
- By applying Flash Fill or the Paste Special feature, the user successfully retains the merged data as permanent text values in the new column, avoiding #REF! errors when source columns are deleted.
Ensure your worksheet does not have any active filters or hidden rows, as these can interfere with applying formulas or using the Flash Fill tool across the entire column.
Use Flash Fill to Combine and Keep Data (Fastest Method)
Flash Fill is the quickest way to combine text without using formulas, producing static text that allows you to delete the original columns immediately.
Excel's Flash Fill feature recognizes data patterns and automatically fills down the column with permanent text values, bypassing the need for formulas completely.
Type the desired combined text from columns A and B into the first data cell of column C (e.g., C2) exactly as you want it to appear, and press Enter.
Select the cell directly below your entry (C3) and press the keyboard shortcut Ctrl+E. Excel will automatically populate the remaining cells in the column based on the pattern you provided.
Verify the data in column C is correct. Since Flash Fill outputs static text rather than dynamic formulas, you can now safely highlight columns A and B, right-click the headers, and select Delete.

Merge with a Formula and Convert to Values
If Flash Fill doesn't detect your pattern correctly, you can use a concatenation formula followed by the Paste Special feature to lock in the text values.
Easily Merge and Convert Spreadsheet Data in WPS Office
WPS Spreadsheet offers powerful built-in tools like Flash Fill, concatenation formulas, and Paste Special to help you organize your data effortlessly without risking data loss.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the columns you want to merge.
- 2. Apply Flash Fill or Formulas: Enter your desired text pattern in a new column and press Ctrl+E, or use a combination formula to merge the cells.
- 3. Convert to Values: If using a formula, copy the merged column, right-click, and select Paste Special > Paste Values to lock in the text.
- 4. Clean up your data: Select the original source columns, right-click the column headers, and delete them without affecting your new merged data.

Frequently Asked Questions
Why do I get a #REF! error when I delete the original columns?
This happens because the newly merged column relies on a formula that actively references the original columns. When you delete the source columns, the formula breaks because the data it points to no longer exists. Using 'Paste Special > Values' converts the dynamic formula to static text, preventing this error.
Can I use Flash Fill to merge data on a Mac?
Yes, Flash Fill is available on Mac versions of modern spreadsheet software, including Excel and WPS Office. The shortcut is typically Control+E, or you can find the Flash Fill button under the Data tab on the top ribbon.
How do I export only the newly merged column to a CSV file?
Before saving your file as a CSV, you can simply delete the unwanted source columns (after making sure you have converted the merged data to values). Alternatively, you can copy only the final merged column, paste it into a new blank workbook, and save that new workbook as a CSV file.




