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

[amitash]

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

[C#Jaap]

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

[amitash]

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

[C#Jaap]

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

[QVeen72]

Hi,

Try this :

Selection.NumberFormat = "#,##0.00"

Regards
Veena