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

Question

6 Years Ago

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

excel format number numeric separator string vba visual-basic-6

3 Contributors
4 Replies
53 Views
6 Years Discussion Span
Latest Post 6 Years Ago by QVeen72

C#Jaap 5 · Answer 1 · 2012-08-01T13:22:57+00:00

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 · Answer 2 · 2012-08-02T03:49:15+00:00

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

C#Jaap 5 · Answer 3 · 2012-08-02T08:21:13+00:00

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 104 · Answer 4 · 2012-08-02T13:55:29+00:00

Hi,

Try this :

Selection.NumberFormat = "#,##0.00"

Regards
Veena