5 Years
Discussion Span
Last Post by QVeen72

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


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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.