0

I'm trying to make something like a str_pad function in VBA, returning leading zeros ahead monetary values.

For example, if I want to pad it with 6 digits:

Input:
$ 423,67
Output:
000423

So far, it can add those leading zeros, but I cant figure out a way to implement it when it doens't have decimals:

Input:
$ 423,00
Current output:
000423
Desired output:
042300

Since the user usually doesn't include zeros after the comma, my code should be able to put them on the output.

Code:

Function str_pad(text As Variant, totalLength As Integer, padCharacter As String) As String
    If totalLength > Len(CStr(text)) Then
        'str_pad = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
    Else
        str_pad = text
    End If
End Function
BigBen
  • 46,229
  • 7
  • 24
  • 40
Igor
  • 913
  • 1
  • 8
  • 18
  • Your examples of `423,67 => 000423` and `423,00 => 042300` seem to contradict each other, otherwise it would have been a duplicate of [Add leading zeroes/0's to existing Excel values to certain length](https://stackoverflow.com/q/3992541/11683). – GSerg May 01 '18 at 16:34
  • [This](https://stackoverflow.com/q/14534360) might be of interest... completely overkill, too, but interesting regardless ;-) – Mathieu Guindon May 01 '18 at 17:02

1 Answers1

0

How about changing your Function to something like below, this will format the numbers as desired and then remove the decimal delimiter, so the numbers show as expected, I've omitted the totalLength as this will pad the numbers with 6 digits:

Function str_pad(text As Variant, padCharacter As String) As String
        str_pad = Format(text, "0000.00")
        str_pad = Replace(str_pad, ".", "")
End Function

To use this, you can use a formula like: =str_pad(A1,0)

Xabier
  • 7,587
  • 1
  • 8
  • 20
  • Is the non-EN-US 'comma-as-decimal-separator' going to work in the very EN-US-centric VBA without a locale modifier? Your format mask might have to use a period regardless of the system's regional decimal separator character. –  May 02 '18 at 01:57
  • @Jeeped thanks for your comment, initially I did use periods, but when I realized that the OP used commas, I thought I should amend my answer,.... Anyways, you always learn something by trying, thanks :) – Xabier May 02 '18 at 08:05