Member Avatar

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

Member Avatar

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
Edited by C#Jaap: Didn't read the question well enough
Member Avatar

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

Member Avatar

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

Member Avatar

Hi,

Try this :

Selection.NumberFormat = "#,##0.00"

Regards
Veena