Formula Errors

How to Fix Excel CONCAT Formula Showing as Text Instead of Result

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to resolve an issue where the CONCAT formula displays as a plain text string instead of yielding the combined result, and copying the formula does not update row references.

Product: Excel 2019
Device & OS: not provided
Scenario: Entering a CONCAT formula to combine text values and copying it down a column for multiple rows.
Observed behavior: Excel displays the formula syntax itself rather than the calculated output, and dragging the formula down fails to dynamically update the row references.

Before you start

Verify that your workbook is set to Automatic calculation in the Formulas tab, and press Ctrl + ` (grave accent) to ensure the 'Show Formulas' feature is not accidentally enabled.

Solution 1Recommended

Change Cell Format to General and Recalculate

This solution addresses the most common cause of the issue: the cell being pre-formatted as Text, which forces Excel to treat formulas as standard text strings.

When a cell format is set to Text, Excel stops evaluating any formulas entered into that cell. You must change the number format and then force Excel to re-evaluate the cell contents.

Select the affected cells

Highlight the specific cells or the entire column that is displaying the CONCAT formula instead of the results.

Change the number format

Navigate to the Home tab on the Excel ribbon, locate the Number group, click the format dropdown menu, and select 'General'.

Force recalculation

Double-click the first affected cell to enter editing mode (or press F2 on your keyboard), and then press Enter. The formula will now calculate and display the correct combined text.

Re-copy the formula

Click the small green square (Fill Handle) at the bottom-right corner of the corrected cell and drag it down to apply the working formula to the rest of the rows.

Tip: You can also use the 'Text to Columns' feature in the Data tab and simply click 'Finish' to quickly force recalculation on an entire column at once.

Solution 2

Disable Caps Lock and Re-enter Formula

In certain localized or specific software configurations, having Caps Lock enabled may interfere with standard keyboard entry and shortcut behaviors.

Solve directly with WPS Office

Use WPS Spreadsheet to Effortlessly Combine Data

WPS Spreadsheet provides robust and highly compatible formula calculation capabilities. You can seamlessly use the CONCAT function to merge cell data without worrying about formatting glitches or calculation errors.

1. Open your file in WPS Spreadsheet: Launch WPS Office, click 'Open', and select your spreadsheet file.
2. Apply the CONCAT function: Select the destination cell, type '=CONCAT(' and select the cells you wish to combine, then close the parenthesis and press Enter.
3. Auto-fill the column: Hover over the bottom-right corner of the cell until the cursor becomes a cross, then double-click or drag downwards to instantly apply the formula to all rows.

Fully compatible with Microsoft Excel formulas, functions, and the .xlsx format.Intelligent cell formatting ensures formulas calculate correctly upon entry.Lightweight, fast, and completely free for everyday data processing tasks.

Frequently Asked Questions

Why does my Excel formula show as text instead of the actual result?

This generally happens for two reasons: the cell's number format was set to 'Text' prior to entering the formula, or the 'Show Formulas' auditing tool is turned on in the Formulas tab.

How do I fix Excel formulas not updating when dragged down?

First, ensure your Calculation Options are set to 'Automatic' under the Formulas tab. If they are set to 'Manual', Excel will not refresh the results until you press F9. Second, make sure your formula uses relative cell references (like A1) instead of absolute references (like $A$1).

Is there a shortcut to switch between showing formulas and values?

Yes, you can press the 'Ctrl + `' (grave accent) keys simultaneously to toggle the display between the formula syntax and the calculated results across the entire worksheet.

Excel & Data Problems