excel - Adding thousand-separators and apostrophe to a number won't show the separators unless i remove the apostrophe?

Question

I'm working on a macro to add separators and an apostrophe at the beginning of a number:

Sub Apostrofe()
Selection.NumberFormat = "#,##0"
For Each cell In Selection
    cell.Value = "'" & cell.Value
    Next cell
End Sub

Output for 123456789 is 123.456.789 but when it adds the apostrophe at the beginning, the format is lost. On the formula bar i see '123456789 but the separators won't appear unless i remove the apostrophe from the number.

I tried to concat the number in parts while manually adding the ' and . but the result is the same. Adding the apostrophe in the Format of the code added it on the cell but it wouldn't appear on the formula bar.

If i manually write the apostrophe + number with separators it works but i receive hundreds of numbers which must be formatted this way.

How can i modify the macro to do what i need?

question from:https://stackoverflow.com/questions/66053359/adding-thousand-separators-and-apostrophe-to-a-number-wont-show-the-separators

Answer 1

If you use an apostrophe the value will be converted to text.

It seems that in your example you do not change the value itself (to the text equivalent containing the formated number), you just apply the format to the cell and then overwrite it with '.

I think you should try following code:

cell.NumberFormat = "@"    ' set cell format to text
cell.Value = Format(cell.Value, "#,##0")    ' Format() returns formated number as a string