Excel Error Codes

Fix Excel #VALUE! Errors When Converting CSV Text to Numbers

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

The user needs to convert text values from a GB18030 CSV file into numbers without Excel returning #VALUE!.

Fix Excel #VALUE! Errors When Converting CSV Text to Numbers
Product
Microsoft Excel for Mac
Device & OS
macOS
Scenario
A CSV file encoded in GB18030 without a byte-order mark is opened in Excel, and a formula is used to convert its text values to numbers.
Observed behavior
The conversion returns #VALUE! with an English system language but works with Simplified Chinese. Text to Columns converts the values successfully.
Before you start

Check a few affected cells for currency symbols, spaces, and decimal or thousands separators. Confirm the CSV encoding and note which separators the source data uses before importing or converting it.

Solution 1Recommended

Convert the affected column with Text to Columns

Use this workaround when VALUE or adding zero returns an error but the cells contain recognizable number text.

1
Select the values

In Excel, select the column or range containing the text values you want to convert.

2
Open the conversion wizard

Choose Data > Text to Columns. Select Delimited and click Next.

3
Set the number format

Clear any delimiter selections that would split the values, then click Next. Choose General; if Advanced is available, set the decimal and thousands separators to match the source data.

4
Convert and check

Click Finish, then inspect several converted cells and retry your formula. Confirm that decimals and thousands have the expected values.

Convert the affected column with Text to Columns
Check unusual characters: Text to Columns may not convert values containing unrecognized currency symbols or nonprinting characters. Inspect and clean those characters first.
Free Microsoft Office alternative

Work with CSV data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. It supports common Office formats and CSV files, making it easy to move spreadsheet work between applications. It does not fix Excel's import or locale settings.

Open and review CSV data in a spreadsheet interfaceCompatible with Microsoft Excel formats such as XLSX and XLSFamiliar tools make the transition from Excel easierFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does VALUE return #VALUE! for text that looks like a number?

The text may contain unrecognized symbols, spaces, or separators, or the CSV may have been decoded incorrectly. Inspect the cell contents and compare the decimal and thousands separators with the ones Excel expects.

How can I convert the values without using VALUE?

Select the affected cells and choose Data > Text to Columns. Keep the values in one column, select General, and click Finish. Check the results, especially decimal values.

Does a CSV file need a byte-order mark to work in Excel?

Not necessarily. A file without a byte-order mark can still be imported, but Excel may not identify its encoding as intended. Use the import wizard to select the encoding when that option is available.

What if GB18030 is not listed in Excel's import options?

Use a text editor that supports GB18030 to save a copy of the file as UTF-8, then import the copy in Excel and select UTF-8 if prompted. Verify the characters and numbers after import.