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

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")
'or
.... = "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

Hi,

Try this :

Selection.NumberFormat = "#,##0.00"

Regards
Veena