Formula Errors

Fix an Excel Concatenation Formula That Works on One Sheet but Not Another

Steven ParkerSteven Parker Nov 18, 2026 871 views

Question details

Determine why a CONCAT or ampersand formula works in a new worksheet but not in an existing one.

Fix an Excel Concatenation Formula That Works on One Sheet but Not Another
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to join text or cell values with the same formula across worksheets.
Observed behavior
The concatenation formula fails or behaves differently in the existing worksheet.
Before you start

Compare the formula and referenced cells on both worksheets. Note whether the affected cell displays the formula itself, an error, or an outdated result.

Solution 1Recommended

Change Text-formatted cells to General and re-enter the formula

Use this fix when Excel displays a formula as text instead of calculating it.

1
Check the cell format

Select the affected cell and open Home > Number Format. If it shows Text, change it to General.

2
Re-enter the formula

Double-click the cell or select it and press F2, then press Enter. Changing the format alone does not convert an existing text entry into a formula.

3
Check the formula prefix

Select the cell and inspect the formula bar. Remove any leading apostrophe or space before the equals sign, then press Enter.

4
Check formula display

If formulas appear across the worksheet, select Formulas > Show Formulas to turn that display mode off.

Change Text-formatted cells to General and re-enter the formula
Concatenate text in WPS Spreadsheets

Join cell values with a formula in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use the ampersand operator to combine text and cell values.

  1. 1. Open the workbook: In WPS Spreadsheets, open the workbook and select the worksheet you want to edit.
  2. 2. Set the cell format: Select the destination cell and choose General from the number format menu on the Home tab.
  3. 3. Enter the formula: Type ="Hello "&C9 in the cell, replacing C9 with the cell containing the value you want to join, and press Enter.
  4. 4. Save the workbook: Select File > Save or Save As and choose an Excel format if you need to share the workbook with Excel users.
Combine text and cell values with an ampersand formulaOpen and edit Microsoft Excel XLSX and XLS workbooksUse familiar spreadsheet menus and a formula barWork with spreadsheets using a free Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show my concatenation formula instead of its result?

The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General under Home > Number Format, re-enter the formula, and check Formulas > Show Formulas.

Why does CONCAT return a #NAME? error?

The Excel version opening the workbook may not support CONCAT, or the function name may be misspelled. Check the formula bar and try the ampersand operator or CONCATENATE.

Why does my formula return an error when C9 contains an error?

Concatenation formulas can propagate errors from referenced cells. Select C9, identify and fix its error, and then check the formula result again.

How do I make the formula update automatically?

In Excel, open Formulas > Calculation Options and select Automatic. Also confirm that the formula references the cells you intend to use.