Format number in Msgbox

[hoanganhdl]

Sub test()


    Application.DecimalSeparator = ","
    Application.ThousandsSeparator = "."
    Application.UseSystemSeparators = False


    MsgBox Format("123456", "#,##0")


Exit Sub

I want result is 123.456 but it is 123,456.

 

[Sektor]

MsgBox Format("123456", "#\.##0")

 

[friel300]

Sub test()
Dim sNum As String


    sNum = Format("123456.4", "#"".""##0")


    MsgBox sNum


End Sub

It comes thru as a String but it works.

 

[friel300]

MsgBox Format("123456", "#\.##0")

Never seen that before what does that slash mean?

 

[Rick Rothstein]

Never seen that before what does that slash mean?

In some script languages, it is called an "escape character" and it means treat the next character as just a character without any special meaning; hence, \. will be interpreted as a period and not a decimal point.

 

[friel300]

Oh thanks, I'm going to have a look into this.

 

[Rick Rothstein]

Oh thanks, I'm going to have a look into this.

Type the function name Format in the Immediate Window (press CTRL+G if you don't see it) inside the VB editor and then, with the text cursor next to or inside the word, press the F1 key to bring up the help file for that function... scroll to the bottom of the help page and look at the next-to-last entry in the displayed table. You might also find this link of interest...

https://en.wikipedia.org/wiki/Escape_character