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

Something like this does change the representation in your worksheet.

Sub ChangeActiveCellFormat()
    ActiveCell.NumberFormat = "#,##0.00"
End Sub

Depending on you local settings of Windows Excel uses a . or a , to separate the thousands.

This however converts the content of the active cell into text and put it in the cell directly beneath.

Sub FormatActiveCellToString()
    ActiveCell.Offset(1, 0).Value = Format(ActiveCell, "#,##0.00")
End Sub

Thanks, but for FormatActiveCellToString() , seems it only changes the number formating but did not change to text. Any idea?

Actually the function transforms the content of the active cell to a formatted text. Maybe Excel changes it back to a formatted number. Add some text or ' quote (in front) to it like

.... = "'" & Format(ActiveCell, "#,##0")
.... = "Formatted text: " & Format(ActiveCell, "#,##0")

You can also change the content type in the cell properties to text to prevent Excel transforming text to number.

Hope this helps


Try this :

Selection.NumberFormat = "#,##0.00"