Custom Comma separator

Vimal_Gaur 

To achieve the custom comma grouping you described in Excel, you can use a combination of custom number formatting and formulas. Excel's built-in number formatting does not support this exact format, so we will need to create a custom formula for this purpose. Assuming the input number is in cell A1, you can use the following formula in another cell to achieve the desired output:

=TEXT(INT(A1/100), "0,") & TEXT(MOD(A1,100), "00.00")

Here is what this formula does:

1. INT(A1/100) extracts the integer part of the number divided by 100, effectively grouping the digits by two.
2. TEXT(..., "0,") formats the extracted integer part to have commas as thousand separators.
3. MOD(A1,100) extracts the remainder when dividing by 100, which represents the last two digits.

1. TEXT(..., "00.00") formats the extracted remainder as a decimal with two digits.

This formula will give you the desired custom comma grouping for the given inputs:

• 12 will become 12.00
• 123 will become 1,23.00
• 1234 will become 12,34.00
• 12345 will become 1,23,45.00

• 123456 will become 12,34,56.00

You can adjust the cell references and the formula to match your specific Excel setup as needed.

Additional, using Excel's "Format Cells" feature, you can apply a custom number format to achieve a similar result. Excel's standard number formatting does not support the exact format you described (grouping digits by twos with commas). Instead, you can use a custom format to display the numbers in a somewhat similar way.

Here is how you can do it:

1. Select the cell or range of cells where you want to apply the custom number format.
2. Right-click and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, go to the "Number" tab.
4. In the "Category" list on the left, select "Custom."

1. In the "Type" field on the right, enter the following custom number format:

##\,##0.00

1. Click "OK" to apply the custom number format.

Now, the selected cells will display the numbers in a format similar to what you described, with commas as thousand separators. Here is how it will look based on your examples:

• 12 will be displayed as 12.00
• 123 will be displayed as 1,23.00
• 1234 will be displayed as 12,34.00
• 12345 will be displayed as 1,23,45.00

• 123456 will be displayed as 12,34,56.00

Please note that this format will group digits by threes with commas, which is a more common thousand separator format. If you specifically require grouping digits by twos, the custom formula approach I mentioned earlier would be necessary. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.