How to Convert Text with Comma Thousands Separators to Numbers in WPS Office Spreadsheets
Question details
The user needs to turn imported CSV values such as 10,562.98 from text into calculation-ready numbers without changing regional settings.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- After importing a CSV file, numeric-looking entries use a period as the decimal separator and a comma as the thousands separator, but the local system expects different separators.
- Observed behavior
- The imported entries remain text instead of true numbers, so they cannot be used directly in calculations.
Open the WPS Office Spreadsheets workbook that contains the imported CSV values and locate the column with text numbers. Confirm that the source format uses a period as the decimal separator and a comma as the thousands separator, such as 10,562.98.
Convert the text with NUMBERVALUE
Use NUMBERVALUE when the source text always uses a period as the decimal separator and a comma as the thousands separator.
NUMBERVALUE is the most direct way to convert a text string to a number without changing regional settings, because you specify the decimal and group separators explicitly.
Click the cell next to the first imported value, for example B1, so the result does not overwrite the source data.
Type =NUMBERVALUE(A1,".",",") and press Enter. The first argument is the source text, the second is the decimal separator, and the third is the thousands separator.
Select B1 again and drag the fill handle down through the last data row, or double-click the fill handle to fill the column.
Select the formula results, press Ctrl+C, right-click the destination or original cells, and choose Paste Special > Values to keep static numbers.
Convert with SUBSTITUTE and VALUE when NUMBERVALUE is unavailable
Use this formula in older spreadsheet versions or if NUMBERVALUE is not recognized, and when your local system expects a comma decimal separator.
Convert Text Numbers to Real Numbers in WPS Office Spreadsheets Without Changing Regional Settings
WPS Office Spreadsheets lets you keep your current regional settings and still convert imported CSV text such as 10,562.98 into real numbers. Use the built-in NUMBERVALUE function, fill it down a column, and paste values for clean, calculation-ready data.
- 1. Open your workbook: Open the WPS Office Spreadsheets workbook containing the imported CSV values.
- 2. Select the first result cell: Click the cell next to the first text number, for example B1.
- 3. Apply the NUMBERVALUE formula: Type =NUMBERVALUE(A1,".",",") and press Enter to convert the first value.
- 4. Fill down the formula: Drag the fill handle from B1 through the last data row to convert the entire column.
- 5. Paste as values if needed: Copy the formula results, right-click the original values, and choose Paste Special > Values to replace the text.
Frequently Asked Questions
Why does WPS Office Spreadsheets store 10,562.98 as text after a CSV import?
The file uses a period as the decimal separator and a comma as the thousands separator, but your system is set to use a comma as the decimal separator. Because the formats do not match, the program keeps the entry as text. Use =NUMBERVALUE(A1,".",",") to convert it explicitly.
What is the fastest way to convert an entire column of these text values?
Enter =NUMBERVALUE(A1,".",",") in a helper cell, then double-click or drag the fill handle down to the last row. If you need static values, copy the results and paste them as values.
Do I need to change my Windows or WPS regional settings to fix this?
No. The NUMBERVALUE formula specifies the decimal and thousands separators directly, so you can keep your current regional settings unchanged.
Why do I see “Not trying to type a formula?” when entering the formula?
The cell may be formatted as Text, or the formula may be missing the leading equals sign. Select the cell, set the number format to General, and start the formula with =.
Can I use this method for other separators, such as 10.562,98?
Yes. Adjust the function arguments to match the source format. For example, for a format that uses a period as the thousands separator and a comma as the decimal separator, use =NUMBERVALUE(A1,",",".").
