Function Problems

How to Convert Merged Excel Text to Permanent Values Before Deleting Columns

Khadija KhanKhadija Khan Nov 10, 2026 869 views

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.

How to Convert Merged Excel Text to Permanent Values Before Deleting Columns
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the combined text manually

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.

2
Activate the Flash Fill feature

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.

3
Safely delete the original columns

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.

Use Flash Fill to Combine and Keep Data (Fastest Method)
Flash Fill Requirement: Flash Fill works best when there is a clear, consistent pattern in your data. If the results are inconsistent, the formula method is a better alternative.
Work Smarter with WPS Spreadsheet

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. 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the columns you want to merge.
  2. 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. 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. 4. Clean up your data: Select the original source columns, right-click the column headers, and delete them without affecting your new merged data.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in Flash Fill (Ctrl+E) and Paste Special for rapid data formatting and cleanup.Lightweight, fast, and completely free to use for everyday spreadsheet tasks.Intuitive, tabbed interface similar to Microsoft Office for a zero-learning-curve transition.
microsoft office alternative - wps office

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.