Number format

[excelliot]

hi in excel comma format gives 3 digit separter e.g. 1,234,567,890

can i have format like this 1,23,45,67,890

i.e. first 3 digit comma separater form right side then 2 comma seprater onwards for remaining digits

[sheeeng]

Hi all,

I'm also cannot do that formatting after I tried the codes below..

Range("A1").Select
ActiveCell.FormulaR1C1 = "1234567890"
Selection.NumberFormat = "#,##,##,##,###"

Sorry...

[excelliot]

This i know but when number of digit fall below it it doesnot work

bcoz i'm not sure about number of digits.

[Bob Phillips]

hi in excel comma format gives 3 digit separter e.g. 1,234,567,890

can i have format like this 1,23,45,67,890

i.e. first 3 digit comma separater form right side then 2 comma seprater onwards for remaining digits

[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

[excelliot]

[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

it workjs upto 9 digits

what if it is more then 9 digits?

any vba macro can solve it?

[Bob Phillips]

it workjs upto 9 digits

what if it is more then 9 digits?

any vba macro can solve it?

Okay, but this changes it to text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i  As Long
On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
        With Target
            For i = Len(.Value) - 2 To 1 Step -2
                .Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
            Next i
        End With
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code.

[excelliot]

this is not wprking??????

Okay, but this changes it to text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i  As Long
On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
        With Target
            For i = Len(.Value) - 2 To 1 Step -2
                .Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
            Next i
        End With
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code.

[Bob Phillips]

It is worksheet event code, so you need to add it to the code module of the applicable worksheet.

[excelliot]

i have added , but no macro is displayed in tools>macro
pls tell me how it works

It is worksheet event code, so you need to add it to the code module of the applicable worksheet.

[JKwan]

You don't run the Worksheet Event Code, it executes when there are changes to the worksheet. The VBA is setup only to run IF it is updated in RANGE H1:H10.

XLD, the only problem is that you are adding a comma with an offset of 2 not 3 then 2.

[Bob Phillips]

XLD, the only problem is that you are adding a comma with an offset of 2 not 3 then 2.

How do you work that one out?

Actually, it could do with one change to facilitate editing

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i  As Long
On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
        With Target
            .Value = CStr(Replace(.Value, ",", ""))
            For i = Len(.Value) - 2 To 1 Step -2
                .Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
            Next i
        End With
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

[excelliot]

so this macro isn't giving required format i.e. first 3 digit separtor by comma, then after words 2 digit for remaining

[Bob Phillips]

so this macro isn't giving required format i.e. first 3 digit separtor by comma, then after words 2 digit for remaining

Are you saying it is not working? If so, post your workbook.

[excelliot]

Are you saying it is not working? If so, post your workbook.

my sheet is attached
check it

[Bob Phillips]

[excelliot]

Oopssss
it doesnt work for decimles,
And it cahnges all type of format
i wants it to change that cells which has number format only
& i wants default right aligned, then where to change.

is it possible to make .xla(add ins) of this macro so that it can be applied to all excel file

thanks

[Bob Phillips]

is it possible to make .xla(add ins) of this macro so that it can be applied to all excel file

Well yes, I suppose so. This add-in uses using application events, so we have to have a way of knowing whether it applies to a specific sheet or not, and if so, what range.

What I have done is to use worksheet names. So, when you want this format to apply to a worksheet, create a local name of xld.Format.Range, and give it the range value.

Enjoy!

[excelliot]

Well yes, I suppose so. This add-in uses using application events, so we have to have a way of knowing whether it applies to a specific sheet or not, and if so, what range.

Ehat I have doen is to use worksheet names. So, when you want this format to apply to a worksheet, create a local name of xld.Format.Range, and give it the range value.

Enjoy!

Thanks dere
how to create a local name of xld.Format.Range
i tried by fooll step

insert>name>xld.Format.Range

but it didinot worked

can u explain me in brief stp by step

[Bob Phillips]

Thanks dere
how to create a local name of xld.Format.Range
i tried by fooll step

insert>name>xld.Format.Range

but it didinot worked

can u explain me in brief stp by step

That won't, because as I said, it works by local/worksheet names.

Make sure you have the applicable worksheet selected, say Sheet1.

Insert>Name>Define ..., add a name of Sheet1!xld.Format.Range, and set the range in the refersto box, make sure it is an absolute formula.


A more robust method might be to make two amendments:
- add a menu to add/remove the names
- allow creation of a workbook name, and if this is et, all sheets in that workbook would have this formatting applicable.

[excelliot]

That won't, because as I said, it works by local/worksheet names.

Make sure you have the applicable worksheet selected, say Sheet1.

Insert>Name>Define ..., add a name of Sheet1!xld.Format.Range, and set the range in the refersto box, make sure it is an absolute formula.


A more robust method might be to make two amendments:
- add a menu to add/remove the names
- allow creation of a workbook name, and if this is et, all sheets in that workbook would have this formatting applicable.

i'm unable to do it?

i'm attching sheet & i 've defined name but it is not working ?

pls do it 4 me

thanking u