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?

Ozonize

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)

Bales

Oh wow, I already knew how to do that. Long day at work I suppose. Definitely my preferred method. – Ozonize

Based off the description of PadLeft here msdn.microsoft.com/en-us/library/92h5dc07(v=vs.110).aspx, this code does not match the behavior of PADLEFT since the example: str.PadLeft(2, "forty-two") would return "fo" instead of "forty-two". This would not occur in the original code. – Colour

I edited and fived PADLEFT. Last param should be 16 instead of 8 – Hankering

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

Splotch

You could just add a check in the function to see if the supplied string is longer than the input length and handle it accordingly. – Trinetta

Answer 3

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

Format(number, "    ")

Format(number, "00")

Cass

Doesn't work. Try ? "!" & Format("a"," ") & "!" and you'll get !a!, and with a number : ? "!" & Format(1.23," ") & "!" gives ! ! – Dupleix

When formatting strings padded with spaces, use the "@" format character placeholder instead. See: Format Function (VBA) – Watkins

Answer 4

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

Phile

Nice & easy. However for very large amounts of data, it is 2-3 times slower than the LEFT/RIGHT method – Sowens

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.

Trinetta

This happens to be very useful for padding data in Excel for loading character strings > 255 long into SQL Server. Thanks for the tip! – Diatom

If you use StrReverse twice, you can use the method described in this post for left padding. – Mclaren

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)

Schleiermacher

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.

Palaeogene

Standing on the shoulder of "gaints" this is probably the most robust solution. One could also use Excel.WorksheetFunction.Max although that would make the solution no longer pure VBA/VB Classic. – Impletion

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

Suzy

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

Jacintajacinth