Search code examples
stringvbastring-formattingnumber-formattingseparator

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)


Solution

  • 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).