Formula Errors

Fix Excel #VALUE! Error Caused by Numbers Stored as Text

Elise Williams Oct 23, 2026 869 views

Question details

The user needs to resolve a #VALUE! error that occurs when spreadsheet arithmetic formulas attempt to calculate cells containing numbers stored as text.

Fix Excel #VALUE! Error Caused by Numbers Stored as Text

Product: Spreadsheets
Device & OS: not provided
Scenario: Performing mathematical operations (such as addition with +) on multiple cells that appear to contain numbers but are actually formatted as text.
Observed behavior: Formulas like =+C6+D6+E6 return a #VALUE! error, while referencing a single text cell (like +C6) merely displays its content without calculating or formatting it.

Before you start

Look for a small green triangle in the top-left corner of the problematic cells, which is the spreadsheet's built-in warning indicating that a number is currently stored as text.

Solution 1Recommended

Use the Error Checking Warning to Convert to Number

This is the fastest and most highly recommended method for quickly converting text-formatted cells back into standard numeric values so formulas can calculate properly.

When a spreadsheet detects a number stored as text, it flags the cell. Utilizing this built-in flag is the safest way to convert data without altering adjacent columns.

Select the problematic cells

Highlight all the cells containing the numbers stored as text. You can select single cells, ranges, or entire columns.

Click the warning icon

Look for the yellow diamond icon with an exclamation point that appears next to your selection and click on it to open the context menu.

Select Convert to Number

From the dropdown menu, choose 'Convert to Number'. Your mathematical formulas referencing these cells will instantly recalculate and output the correct result instead of #VALUE!.

Use the Error Checking Warning to Convert to Number

Formatting Options: Once converted to pure numbers, you can easily apply the Accounting format from the Number group on the ribbon to display currency properly.

Solution 2

Use the Text to Columns Feature

Ideal for converting entire columns of text-formatted numbers at once, especially when importing data from external software or CSV files.

Solution 3

Apply the VALUE Function in Your Formulas

Use this method if you cannot change the source data formatting and need to force the calculation dynamically within the formula itself.

Spreadsheet Troubleshooting

Resolve Formula Errors Instantly with WPS Spreadsheet

WPS Spreadsheet offers powerful data handling tools, identical formula syntax, and automated error-checking features to quickly convert text into numbers, ensuring your mathematical calculations work flawlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the #VALUE! errors.
2. Highlight the flagged cells: Select the cells with the green error triangles in the corner.
3. Convert instantly: Click the warning prompt icon that appears next to the selection and choose 'Convert to Number'.
4. Verify your formulas: Check your sum formulas; they will automatically recalculate and display accurate totals.

Fully compatible with Microsoft Excel (.xlsx) formats, formulas, and functionsBuilt-in error checking automatically flags numbers stored as textFree, lightweight, and incredibly fast even when processing large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does my addition formula return a #VALUE! error?

The #VALUE! error occurs when an arithmetic formula (like addition using the + operator) encounters text, spaces, or special characters instead of numbers. Even if a cell looks like a number, it might be stored as text, which prevents mathematical calculation.

How do I fix the default currency format showing the wrong symbol?

The default currency symbol applied by the Accounting format depends on your operating system's Language and Region settings. You can fix this by navigating to the Windows Control Panel, selecting 'Clock and Region', and modifying your system's currency and region formats.

Can I use the Accounting format on converted numbers?

Yes. Once you have successfully converted the text back into pure numbers using Error Checking or Text to Columns, you can select the cells, go to the Home tab, and choose 'Accounting' from the Number Format dropdown menu.

Excel & Data Problems