How to Format Numbers with Two-Digit Comma Grouping in WPS Office and Excel
Question details
The user wants to format spreadsheet values so the integer part uses comma groups of two digits, for example 1,23,45.00, instead of the standard three-digit grouping.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with values that need a regional, ledger, or reporting display such as 1,23,45.00 and wants to apply that display in a workbook without using the standard three-digit grouping.
- Observed behavior
- The standard number format and Format Cells dialog only group digits by three, so a value such as 12345 is shown as 12,345.00 or another standard pattern instead of 1,23,45.00.
Open the workbook containing the values and identify the range that needs the two-digit display. Decide whether you can use a helper column, because this display cannot be applied through the standard Format Cells number dialog.
Format Two-Digit Commas with a Custom LET Formula
Use this macro-free formula when your WPS Office or Excel version supports dynamic array functions such as SEQUENCE and TEXTJOIN; it handles negative numbers, decimal places, and any integer length.
WPS Spreadsheets and Excel do not have a built-in number-format code for two-digit comma groups. Standard number formats such as ##\,##0.00 still use three-digit grouping.
This formula converts the rounded absolute value to text, divides the integer part into two-digit chunks from the right, and then appends the sign and two decimal places.
Open WPS Spreadsheets or Excel and click the helper cell where the formatted text should appear, for example B1.
Type the following formula and press Enter: =LET(n,ROUND(ABS(A1),2),sign,IF(A1<0,"-",""),s,TEXT(INT(n),"0"),sp,IF(MOD(LEN(s),2)=1,"0"&s,s),total,LEN(sp)/2,chunks,MID(sp,(SEQUENCE(total)-1)*2+1,2),first,TEXT(VALUE(INDEX(chunks,1)),"0"),rest,IF(total>1,TEXTJOIN(",",TRUE,INDEX(chunks,SEQUENCE(total-1,,2))),""),dec,RIGHT(TEXT(n,"0.00"),2),sign&first&IF(rest="","",","&rest)&"."&dec)
If your values are in A1:A100, drag the fill handle from B1 down or use the formula in each helper cell.
Use a VBA User-Defined Function for Older Versions or Reusable Formatting
Use this when your version does not support dynamic array functions or when you want a reusable function that can be applied to any cell directly.
Apply Two-Digit Comma Grouping in WPS Office Spreadsheets
WPS Office Spreadsheets can open your Excel workbook and perform the custom formula or macro display in a helper column, so you can keep the original data and still see 1,23,45.00-style results free.
- 1. Download WPS Office: Go to the official WPS Office download page and install WPS Spreadsheets on your device.
- 2. Open the Excel workbook: Launch WPS Spreadsheets, click Open, and select the .xlsx or .xlsm file containing the numbers to format.
- 3. Add a helper formula: Click an empty cell next to the value and enter the custom two-digit grouping formula or VBA function from above.
- 4. Fill the formatted display: Drag the fill handle down the helper column to display two-digit comma groups for the remaining values.
Frequently Asked Questions
Why doesn't Excel or WPS have a built-in two-digit comma number format?
The standard number format engine always groups digits by three when a comma is used. A custom format such as ##\,##0.00 still applies three-digit grouping, so it cannot produce 1,23,45.00. Use a formula or VBA function instead.
Will the custom formula change my original numbers?
No. The formula displays formatted text in a helper cell. The source cell remains unchanged, so you can still use the original value in calculations.
How can I get two decimal places in the result?
Both methods append two decimal places: the formula uses TEXT(n,"0.00") and the VBA function uses Format(...,"0.00"). Your displayed result includes values such as 1,23,45.00.
Can I use the VBA function in WPS Office?
WPS Office supports macro-enabled workbooks on supported Windows editions. Save the file as .xlsm or WPS macro-enabled format and enable macros if prompted.
What versions are required for the formula method?
The formula requires dynamic array functions including LET, SEQUENCE, and TEXTJOIN. If your version does not support them, use the VBA function instead.
