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!