How to Right Justify Numbers Using Format$ (95945)

SUMMARY

NOTE: The technique described in this article works only with monospace fonts like Courier New, not proportional fonts like Times New Roman.

Use the following two-step process to right justify numbers in a string by using the format$ function:

1. Format the number into a string by using the usual numeric conversion characters (0 # . ,).
2. Format the resulting string by using a format string consisting of a number of @ characters equal to the length of the format string used in step 1.

The following example Sub procedure formats several numbers using the seven character formats $##0.00 and @@@@@@@:

   Sub Form_Click ()
      Print "|" + Format$(Format$(1.5, "$##0.00"), "@@@@@@@") + "|"
      Print "|" + Format$(Format$(12.5, "$##0.00"), "@@@@@@@") + "|"
      Print "|" + Format$(Format$(123.5, "$##0.00"), "@@@@@@@") + "|"
   End Sub
				

Here is the output:

   |  $1.50|
   | $12.50|
   |$123.50|
				

   Function rFormat (value As Variant, fmt As String) As Variant
      rFormat = Format(Format(value, fmt), String$(Len(fmt), "@"))
   End Function