How to Format Numbers as 100,000 Without Changing Regional Settings in WPS Office Spreadsheets
Question details
The user needs to display a numeric value as 100,000 instead of the Indian-style 1,00,000 without altering the computer's regional settings.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is preparing or reviewing a spreadsheet and must present figures in Western grouping, regardless of the system locale.
- Observed behavior
- WPS Office Spreadsheets shows the value using the current regional grouping, so 100000 appears as 1,00,000.
Make sure you have WPS Office Spreadsheets open and identify the cells containing the Indian-style numbers. Insert an empty helper column next to those cells so the formula can display the Western format without overwriting the original values.
Use a TEXT formula in a helper column to display 100,000 without changing regional settings
This method creates the Western grouping in a separate column while leaving the original numeric values and system regional settings unchanged.
In current spreadsheet versions, custom number formats cannot reliably override the operating system's digit-grouping rules. A formula using the TEXT function lets you specify the exact separator pattern and returns a text result with the desired 100,000 appearance.
In WPS Office Spreadsheets, open your workbook and click the header of the column immediately to the right of the original numbers, then press Ctrl+Shift+= to insert a new helper column.
In the first helper cell next to your value, type =TEXT(A2,"#,##0") — replacing A2 with the actual cell reference — and press Enter.
Hover over the bottom-right corner of the cell, drag the fill handle down to copy the formula to the remaining rows, or double-click the fill handle to fill the range automatically.
Select the helper column, press Ctrl+C, right-click the target cells, and choose Paste Special > Values if you need the displayed 100,000 result as static data.
Display 100,000-Style Numbers in WPS Office Spreadsheets Without Regional Changes
Use the WPS Office TEXT function in a helper column to format values with Western thousands separators such as 100,000 while leaving system regional settings untouched. WPS Office Spreadsheets keeps the workflow simple, free, and compatible with Excel files.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the file that shows Indian-style numbers such as 1,00,000.
- 2. Insert a helper column: Click the column header to the right of the source data and press Ctrl+Shift+= to add an empty helper column.
- 3. Enter the TEXT function: In the first helper cell, type =TEXT(A2,"#,##0") or =TEXT(A2,"#,##0.00") and press Enter.
- 4. Fill and finalize: Drag the fill handle down the column, then select the helper column and use Copy > Paste Special > Values if you want the results as static text.
Frequently Asked Questions
Why does WPS Office display 100000 as 1,00,000?
WPS Office Spreadsheets follows the regional format set in the operating system. If the system uses Indian digit grouping, numbers are separated as 1,00,000; switching the OS regional settings changes the default display.
Can a custom number format force the Western 100,000 separator?
In current WPS Office and Excel versions, a custom number format cannot reliably override the system digit grouping. Use a TEXT formula in a helper column for a fixed Western-style display.
Does the TEXT formula change the original value?
No. It creates a text representation in the helper cell, so the original numeric cell remains unchanged. Use Paste Special > Values if you want to replace the source with the displayed text.
How do I include two decimal places, such as 100,000.00?
Use =TEXT(A2,"#,##0.00") in the helper cell. The format codes after the decimal point control the number of displayed decimal places.
Will the formatted result remain when the file is opened in Microsoft Excel?
Yes. The helper column contains text or pasted values and will display the same 100,000 grouping when opened in Microsoft Excel or other compatible spreadsheet applications.
