49

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?

Taylor K.
  • 1,077
  • 2
  • 14
  • 25

8 Answers8

89

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)
LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
  • Oh wow, I already knew how to do that. Long day at work I suppose. Definitely my preferred method. – Taylor K. Aug 21 '12 at 18:47
  • 2
    Based off the description of PadLeft here https://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. – ZaydH Jun 22 '16 at 06:49
  • 1
    I edited and fived PADLEFT. Last param should be 16 instead of 8 – Saibamen Aug 02 '20 at 13:17
18

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
Community
  • 1
  • 1
Brad
  • 11,934
  • 4
  • 45
  • 73
  • 1
    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. – SierraOscar Aug 20 '16 at 07:01
9

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

Format(number, "    ")

Format(number, "00")
Sulaiman
  • 147
  • 4
  • 11
  • 1
    Doesn't work. Try `? "!" & Format("a"," ") & "!"` and you'll get `!a!`, and with a number : `? "!" & Format(1.23," ") & "!"` gives `! !` – AjV Jsy Mar 13 '15 at 11:47
  • When formatting strings padded with spaces, use the "@" format character placeholder instead. See: [Format Function (VBA)](https://msdn.microsoft.com/en-us/library/office/gg251755.aspx) – David Turner May 30 '16 at 00:52
8
Format("abc","!@@@@@@") ' width >= 6; pad right side with spaces
Format("abc","@@@@@@") ' width >= 6; pad left side with spaces
Ernie Thomason
  • 1,579
  • 17
  • 20
  • Nice & easy. However for very large amounts of data, it is 2-3 times slower than the LEFT/RIGHT method – iDevlop Nov 17 '17 at 15:02
3

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.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • This happens to be very useful for padding data in Excel for loading character strings > 255 long into SQL Server. Thanks for the tip! – FreeMan Jan 31 '17 at 20:34
  • 1
    If you use [**StrReverse**](https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/strreverse-function) twice, you can use the method described in this post for left padding. – Mark Fernandes May 31 '19 at 18:34
1

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)
francesco
  • 7,189
  • 7
  • 22
  • 49
cco
  • 33
  • 5
1

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.

MarkBC
  • 95
  • 8
  • 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. – John Bentley Jan 07 '21 at 21:39
0

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
Zac
  • 56
  • 4