how to format numbers, digit grouping ,as per regional settings

[Guest]

I am using XP with office 2000. I have enetered the regional settings for
India, which didgit groupings as ##,##,###.## But the excel does not
recognises this grouping system. How to configure to get this digit gruoping.

 

[Guest]

I only tried this on one number and in one simple formula, but this Custom
Format worked with that limited testing:
##\,##\,###.##

tj

 

[Guest]

If VBA is a viable answer for you, you might be able to use an event
procedure to format the cell on a case by case basis. That would save you
from haivng the opening commas my first post will give you for short numbers.
Something like this in the Worksheet_SelectionChange procedure for the
appropriate worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cl As Range
Set cl = ActiveCell
Select Case cl.Value
Case Is < 999
cl.NumberFormat = "###.00"
Case Is < 99999
cl.NumberFormat = "##\,###.00"
Case Is < 9999999
cl.NumberFormat = "##\,##\,###.00"
Case Else
cl.NumberFormat = "General"
End Select
End Sub

To get this in place, right-click on the worksheet tab and choose view tab.
There wil be two drop-downs near the top of the Window. The one on the left
probably has the word General in it. Click on it and change it to Worksheet.
You can copy and paste all but the first and last line of the above code
into the middle of the Worksheet_SelectioChange procedure.

hope that helps,

tj
If the formatting doesn't happen directly after you type in the cell, click
back on the cell. You may have to adjust your macro security> Tools | Macros
| Security. Medium will let you enable macros when you open the workbook.

 

[Guest]

i shall try and come back, thank you
mayank

I only tried this on one number and in one simple formula, but this Custom
Format worked with that limited testing:
##\,##\,###.##

tj