Fix Excel #VALUE! Errors When Converting CSV Text to Numbers
Question details
The user needs to convert text values from a GB18030 CSV file into numbers without Excel returning #VALUE!.

- 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.
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.
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.
In Excel, select the column or range containing the text values you want to convert.
Choose Data > Text to Columns. Select Delimited and click Next.
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.
Click Finish, then inspect several converted cells and retry your formula. Confirm that decimals and thousands have the expected values.

Reimport the CSV with the correct encoding
Use this approach if characters appear corrupted or Excel may have decoded the GB18030 file incorrectly.
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.

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.




