CODEWITHSUNDEEP
stringvbastring-formattingnumber-formattingseparator
Tuberose
Tuberose

Reputation: 444

String Number format with "/" for thousand separator

The sValA variable.

For example sValA = 23224560.

I need below format:

23/224/560

(Assigning the / character as thousand separator.)

What I was tried:

The Format(23224560, "#,##0") returns: 23,224,560

But Format(23224560, "#/##0") returns: 23224/560 ! (Needed)

Upvotes: 1

Views: 1043

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

You can't use a thousand separator other than the Number Format recognized by your system - your thousand separator appears to be a comma. To use anything other than the system's thousand separator, you can achieve the result you want by repeating the pattern:

?Format(23224560, "###/###/##0")
23/224/560

Of course, that only works if your number is at least 7 digits, and at most 9 digits. You could use a Select block to do the formatting:

Dim sValA As String
sValA = 23224560

Dim result As String

Select Case Len(sValA)
  Case 1 To 3
      result = Format$(sValA, "##0")
  Case 4 To 6
      result = Format$(sValA, "###/##0")
  Case 7 To 9
      result = Format$(sValA, "###/###/##0")
  'Case...
End Select

PS - You'd be better off with the Format$ function which returns a String, as opposed to Format which returns a Variant (String).

Upvotes: 2

Related Questions