VBA Excel: change numeric value to string with 1000 separator (,)

    Hi, I want to change the numeric value in a cell to a string with 1000 Separator(,), e.g. numeric 1000 will to string "1,000" and 123456789 will change to string "123,456,789". How to do that in VBA? Thanks

    Re: VBA Excel: change numeric value to string with 1000 separator (,)


    Code
    Sub test()
    ActiveCell.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;[email protected]_-"
    ActiveCell.Value = "'" & Trim(ActiveCell.Text)
    
End Sub

    Re: VBA Excel: change numeric value to string with 1000 separator (,)


    try

    Code
    Sub test()
Dim r As Range
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
    r.NumberFormat = "@"
    r.Value = Format$(r.Value, "#,#")
Next
End Sub

    Re: VBA Excel: change numeric value to string with 1000 separator (,)


    Thanks, but it cannot cater if the cell value = 0, it will become "-" ... any idea?

    Re: VBA Excel: change numeric value to string with 1000 separator (,)


    Hmm... Both codes fail that one in different ways, it seems.
    A slight tweak to jindon's code (as his is more flexible than mine)

    Code
    Sub test()
    Dim r As Range
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        r.NumberFormat = "@"
        r.Value = Format$(r.Value, "#,0")
    Next
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login