Any method equivalent to PadLeft/PadRight?

Question

Just wondering, is there any equivalent in VBA to VB .NET's PadLeft and PadRight methods?

As of right now, whenever I want to take a string and make it a fixed length with leading spaces, I do a For...Next loop based on the string's length.

For example, I would use the following code to format a string to 8 characters with leading spaces:

intOrdNoLen = Len(strOrdNo)
For i = 1 To (8 - intOrdNoLen) Step 1
    strOrdNo = " " & strOrdNo
Next

Is there a way to do this same thing in fewer lines in VBA?

Answer 1

I don't believe there are any explicit PADLEFT or PADRIGHT functions, but you can use a combination of SPACE and LEFT or RIGHT to prepend spaces to your string, and then grab the right X number of characters.

PADLEFT

strOrdNo = RIGHT(Space(8) & strOrdNo, 8)

If you want a character instead of spaces, you can use STRING instead of space (the example below left-pads with X):

strOrdNo = RIGHT(String(8, "X") & strOrdNo, 8)

PADRIGHT

strOrdNo = LEFT(strOrdNo & Space(8), 8)

strOrdNo = LEFT(strOrdNo & String(8, "X"), 8)

Answer 2

You could use these. Put them in a public module

'NB Fails if input string is longer than the total length

Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
    PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
End Function

Function PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
    PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
End Function

Answer 3

Since we generally pad on the left side, the Format() function is shorter, simpler:

Format(number, "    ")

Format(number, "00")

Answer 4

Format("abc","!@@@@@@") ' width >= 6; pad right side with spaces
Format("abc","@@@@@@") ' width >= 6; pad left side with spaces

Answer 5

You can also use fixed length strings in VBA:

Dim myString As String * 10
    myString = "test"
    Debug.Print myString, "(" & Len(myString) & ")" '// Prints "test          (10)"

although this is only useful for padding on the right.

Answer 6

I solved the problem by reassigning variable.
In my code I get data from workbook cell and limit it to 5 char (if necessary fill with enough 0..):

MB = Right(String(5, "0") & Worksheets("HOME").Range("b3"), 5)
MB = Right(MB, 5)

Answer 7

Merging the top two answers (thanks to LittleBobbyTables and Brad) and noting the helper function max, I would suggest:

Function PadLeft(ByVal text As Variant, ByVal totalLength As Integer, ByVal padCharacter As String) As String
    PadLeft = Right(String(totalLength, padCharacter) & CStr(text), max(totalLength, Len(CStr(text))))
End Function

Function PadRight(ByVal text As Variant, ByVal totalLength As Integer, ByVal padCharacter As String) As String
    PadRight = Left(CStr(text) & String(totalLength, padCharacter), max(totalLength, Len(CStr(text))))
End Function

Public Function max(ByVal x As Variant, ByVal y As Variant) As Variant
  max = IIf(x > y, x, y)
End Function

totalLength might be better named minimumLength, since the entire original string is always returned, possibly causing the result to be longer than minimumLength.

Answer 8

I took a different approach for this answer. Its not the most simple one, but its probably the most general. I used some of the code from LittleBobbyTables and Brad to produce this. Here are several examples on how to use the function:

Sub test()
    Debug.Print PadStr("ABC", 6) 'returns "ABC   "
    Debug.Print PadStr("ABC", 6, "-") 'returns "ABC---"
    Debug.Print PadStr("ABC", 6, , xlHAlignRight) 'returns "   ABC"
    Debug.Print PadStr("ABC", 7, "*", xlHAlignCenter) 'returns "**ABC**"
    Debug.Print PadStr("ABC", 9, "*", xlHAlignDistributed) 'returns "**A**B*C*"
    Debug.Print PadStr("ABC", 7, "*", xlHAlignFill) 'returns "ABCABCA"
End Sub

Here is the function:

Function PadStr(Expression As Variant, length As Integer, Optional padChar As String = " ", Optional alignment As XlHAlign = xlHAlignGeneral) As String
'Pads a string with a given character.
'@Expression - the string to pad
'@length - the minimum length of the string (if @Expression is longer than @length, the original Expression will be returned)
'@padChar - the character to pad with (a space by default)
'@alignment - what type of alignment to use. Uses the XlAlign object for enumeration.
'   xlHAlignLeft            - (Default) Aligns input text to the left
'   xlHAlignGeneral         - Same as Default
'   xlHAlignRight           - Aligns input text to the right
'   xlHAlignCenter          - Center aligns text
'   xlHAlignCenterAcrossSelection       - Same as xlHAlignCenter
'   xlHAlignDistributed     - Distributes the text evenly within the length specified
'   xlHAlignJustify         - Same as xlHAlignDistributed
'   xlHAlignFill            - Fills the specified length with the text
'example: if input is "ABC", " ", "8", see code below for what the output will be given the different direction options
    If Len(Expression) >= length Or (padChar = "" And alignment <> xlHAlignFill) Then
        'if input is longer than pad-length padChar or no input given for padChar (note: padChar doesn't matter when
        'using xlHAlignFill) just return the input
        PadStr = Expression
    ElseIf Len(padChar) <> 1 And alignment <> xlHAlignFill Then
        'give error if padChar is not exactly 1 char in length (again, padChar doesn't matter when using xlHAlignFill)
        'padChar must be 1 char long because string() only accepts 1 char long input.
        Err.Raise vbObjectError + 513, , "input:'padChar' must have length 1." & vbNewLine & "SUB:PadStr"
    Else
        Dim pStr As String, i As Long
        Select Case alignment
            Case xlHAlignLeft, xlHAlignGeneral '(Default)
                '"ABC     "
                PadStr = CStr(Expression) & String(length - Len(CStr(Expression)), padChar)
            Case xlHAlignRight
                '"     ABC"
                PadStr = String(length - Len(CStr(Expression)), padChar) & CStr(Expression)
            Case xlHAlignCenter, xlHAlignCenterAcrossSelection
                '"   ABC  "
                pStr = String(Application.WorksheetFunction.RoundUp((length / 2) - (Len(Expression) / 2), 0), padChar)
                PadStr = pStr & Expression & pStr
            Case xlHAlignDistributed, xlHAlignJustify
                '"  A B C "       ("  A  B C " if lenth=9)
                Dim insPos As Long, loopCntr As Long: loopCntr = 1
                PadStr = Expression
                Do While Len(PadStr) < length
                    For i = 1 To Len(Expression)
                        PadStr = Left(PadStr, insPos) & padChar & Right(PadStr, Len(PadStr) - insPos)
                        insPos = insPos + 1 + loopCntr
                        If Len(PadStr) >= length Then Exit For
                    Next i
                    PadStr = PadStr & padChar
                    loopCntr = loopCntr + 1
                    insPos = 0
                Loop
            Case xlHAlignFill
                '"ABCABCAB"
                For i = 1 To Application.WorksheetFunction.RoundUp(length / Len(Expression), 0)
                    PadStr = PadStr & Expression
                Next i
            Case Else
                'error
                Err.Raise vbObjectError + 513, , "PadStr does not support the direction input ( " & direction & ")." & vbNewLine & "SUB:PadStr"
        End Select
        PadStr = Left(PadStr, length) 'output cannot be longer than the given length
    End If
End Function

Answer 9

You can use functions below to pad with strings instead of single characters:

Function PadLeft(ByVal inputString, ByVal totalWidth, ByVal padding)
  IF Len(padding) < 1 Then padding = " "
  Do While Len(inputString) < totalWidth
    inputString = padding & inputString 
  Loop
  PadLeft = RIGHT(inputString, totalWidth)
End Function

Function PadRight(ByVal inputString, ByVal totalWidth, ByVal padding)
  IF Len(padding) < 1 Then padding = " "
  Do While Len(inputString) < totalWidth
    inputString = inputString & padding
  Loop
  PadRight = Left(inputString, totalWidth)
End Function