Ever needed to control the length of a string to align content and needed to ‘pad’ the string with spaces?!

This can be a common need when generating text files, populating Textboxes, …

Today, I thought I’d share a very simple solution that can be used to pad a string from either side.

The Code

Enum PadSide
    PadLeft = 1
    PadRight = 2
End Enum

'---------------------------------------------------------------------------------------
' Procedure : String_Pad
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Pad a string with spaces
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: Enum PadSide
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput    : Text string to pad
' lTotalLen : Desired total length of the string
' lPadSide  : Which side to pad: PadLeft or PadRight
' bTruncate : If the input string exceeds the desired total length, should it be trimmed
'               to the desired total length
'
' Usage:
' ~~~~~~
'? "'" & String_Pad("Toto", 8, PadRight) & "'"
'   Returns -> 'Toto    '
'? "'" & String_Pad("Toto", 8, PadLeft) & "'"
'   Returns -> '    Toto'
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-10-13              Initial Public Release
' 2         2022-10-19              Made lPadSide optional, default to PadLeft
'                                   Added sInput length check, Added bTruncate
'---------------------------------------------------------------------------------------
Public Function String_Pad(ByVal sInput As String, _
                           lTotalLen As Long, _
                           Optional lPadSide As PadSide = PadLeft, _
                           Optional bTruncate As Boolean = True) As String
    Dim sFormat               As String

    If Len(sInput) > lTotalLen Then
        If bTruncate = True Then
            If lPadSide = PadLeft Then String_Pad = Left(sInput, lTotalLen)
            If lPadSide = PadRight Then String_Pad = Right(sInput, lTotalLen)
        Else
            String_Pad = sInput
        End If
    Else
        sFormat = String(lTotalLen, "@")
        If lPadSide = PadRight Then sFormat = "!" & sFormat
        String_Pad = Format(sInput, sFormat)
    End If
End Function

Usage Examples

Right Padding

We can Right Pad a string by doing:

? "'" & String_Pad("Toto", 8, PadRight) & "'"

which outputs:

'Toto    '

LeftPadding

We can Left Pad a string by doing:

? "'" & String_Pad("Toto", 8) & "'"

which in turn outputs:

'    Toto'

Padding Long Strings

From the left side:

? "'" & String_Pad("Lorem Ipsum is simply dummy text", 8) & "'"

which in turn outputs:

'Lorem Ip'

Or, from the right side:

? "'" & String_Pad("Lorem Ipsum is simply dummy text", 8, PadRight) & "'"

which in turn outputs:

'mmy text'

Or, avoiding truncation altogether

? "'" & String_Pad("Lorem Ipsum is simply dummy text", 8, , False) & "'"

which in turn outputs:

'Lorem Ipsum is simply dummy text'

There you have it, short and sweet! Now you have complete control over the padding and truncation of strings.

Did Someone Say Queries?!

Since we created a Public Function and if we place it in a Standard Module, then we can use this same function directly in a query or a form/report! Just remember that outside of the VBA environment you won’t have the Enum to help you, so you need to enter the numeric values for the lPadSide input argument (1 for left padded, 2 for right padded).

Left Padded Field

As it would appear in Design View:

LeftPaddedField: String_Pad([MyFieldName],20,1)

As it would appear in SQL View:

String_Pad([MyFieldName],20,1) AS LeftPaddedField

Right Padded Field

As it would appear in Design View:

RightPaddedField: String_Pad([MyFieldName],20,2)

As it would appear in SQL View:

String_Pad([MyFieldName],20,2) AS RightPaddedField

Padding With Another Character Than a Space

It can be necessary to pad a string with another character than just a space. For such situation, then you could turn to something like:

Enum PadSide
    PadLeft = 1
    PadRight = 2
End Enum

'---------------------------------------------------------------------------------------
' Procedure : String_PadWithChr
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Pad a string with the specified character
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: Enum PadSide
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput    : Text string to pad
' lTotalLen : Desired total length of the string
' lPadSide  : Which side to pad: PadLeft or PadRight
' sChr      : The character to use to pad the string with, if omitted a space is used
' bTruncate : If the input string exceeds the desired total length, should it be trimmed
'               to the desired total length
'
' Usage:
' ~~~~~~
'? "'" & String_PadWithChr("Toto", 8, PadRight, "|") & "'"
'   Returns -> 'Toto||||'
'? "'" & String_PadWithChr("Toto", 8, PadLeft, "|") & "'"
'   Returns -> '||||Toto'
'? "'" & String_PadWithChr("Lorem Ipsum is simply dummy text", 8, PadLeft, " ") & "'"
'   Returns -> 'Lorem Ip'
'? "'" & String_PadWithChr("Lorem Ipsum is simply dummy text", 8, PadLeft, " ", False) & "'"
'   Returns -> 'Lorem Ipsum is simply dummy text'
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-10-13              Initial Public Release
' 2         2022-10-19              Made lPadSide optional, default PadLeft
'                                   Made sChr optional, default " "
'                                   Added sInput length check, Added bTruncate
'---------------------------------------------------------------------------------------
Public Function String_PadWithChr(ByVal sInput As String, _
                                  lTotalLen As Long, _
                                  Optional lPadSide As PadSide = PadLeft, _
                                  Optional sChr As String = " ", _
                                  Optional bTruncate As Boolean = True) As String
    Dim sPadding              As String
    Dim lInputLen             As Long
    Dim lNoChrs               As Long

    lInputLen = Len(sInput)
    If lInputLen > lTotalLen Then
        If bTruncate = True Then
            If lPadSide = PadLeft Then String_PadWithChr = Left(sInput, lTotalLen)
            If lPadSide = PadRight Then String_PadWithChr = Right(sInput, lTotalLen)
        Else
            String_PadWithChr = sInput
        End If
    Else
        lNoChrs = lTotalLen - lInputLen
        sPadding = String(lNoChrs, sChr)
        If lPadSide = PadLeft Then String_PadWithChr = sPadding & sInput
        If lPadSide = PadRight Then String_PadWithChr = sInput & sPadding
    End If
End Function

Which you can use by doing:

? "'" & String_PadWithChr("Toto", 8, PadRight, "|") & "'"

and which will return:

'Toto||||'

Or

? "'" & String_PadWithChr("Lorem Ipsum is simply dummy text", 12, PadLeft) & "'"

and which will return:

'Lorem Ipsum '

Or

? "'" & String_PadWithChr("Lorem Ipsum is simply dummy text", 8, PadLeft, , False) & "'"

and which will return:

'Lorem Ipsum is simply dummy text'

Or

? "'" & String_PadWithChr("Lorem Ipsum is simply dummy text", 8) & "'"

and which will return:

'Lorem Ip'

…

And of course, the comments regarding using the function in a queries/form/report apply equally here as well.

A Few Ending Remarks

The beauty with these Functions is they:

• Uses no APIs, so no bitness considerations, so it work in both 32 and 64-bit installations
• Uses no application specific methods/properties/…, so it can be used in any VBA application (Excel, Outlook, Word, …)
• Can be used throughout your database (queries, forms, reports, VBA)

Resources on the Subject

String function (Visual Basic for Applications)

Returns a Variant (String) containing a repeating character string of the length specified.