Solution to Excel Text Formatting in UserForm

Question

I'm working with a UserForm and plan to perform calculations in my UserForm. In order to present numbers in a 'correct way as text, I use the text format function.

I use the following syntax: FORMAT(xxxx, "#'###"). All well, however, when xxxx < 1000, I get a string preceded with an apostrophe (that is the 1000s separator I use): 'xxx

Unfortunately, when this happens, I cannot use the string 'xxx for subsequent calculations. Hence CDbl('xxx) does not work.

Any suggestions to circumvent this issue?

Answer 1

For display only purposes:

Sub ForDisplayPurposes()
    Dim xxxx As Long, s As String
    xxxx = 123456
    s = Replace(Format(xxxx, "#,###"), Find:=",", Replace:="'")
    MsgBox s
End Sub

To convert back to a number:

num = CDbl(Replace(s, "'", ""))