0

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?

mdebraaf
  • 1

1 Answers1

0

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

enter image description here

To convert back to a number:

num = CDbl(Replace(s, "'", ""))
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you for your time & contribution. The second bit ( `num = CDbl(Replace(s, "'", ""))` ) is a smart way to circumvent the issue, really helps me out to perform the calculations. The text formatting works also but is a convoluted way to achieve it. Does this mean that for numbers < 1000, the text formatting with the "," the coma will not appear, but with the apostrophe as a separator, the apostrophe will appear? `FORMAT(123, "#'###")` results in `'123` and `FORMAT(123, "#,###")` results in `123` – mdebraaf Feb 01 '21 at 14:33