Left or Right Pad a String with a VBScript Function

If you want to pad strings to a certain length this function can help you do that. You can specify the total length of the string, the character to use for padding and whether to pad to the left or right.

It is useful for creating formatted columns of text in outputs from VBScript scripts or for manipulating cells in Microsoft Excel.

Pad String Function

Here is the pad string function definition:

Function PadString(pString,pLength,pChar,pSide)

  ' This function pads a string to a specified length
  
  ' pString - String to pad
  ' pLength - Required length
  ' pChar   - Single character to use for padding
  ' pSide   - Add padding on the "left" or "right"

  ' If the string is already longer than pLength it will
  ' be truncated.
  
  strString = pString
  
  ' Create padding of required length
  strPadding = String(pLength,pChar)  
  
  If lcase(pSide)="left" then
    strString = strPadding & strString
    strString = Right(strString,pLength)
  else
    strString = strString & strPadding
    strString = Left(strString,pLength)  
  End if
  
  PadString = strString  ' Return string  
   
End Function

To add this to Excel you can:

• Press Alt-F11 to launch the Visual Basic editor
• From the menu select “Insert” then “Module”
• Paste in the function definition

Now you can switch back to your Excel sheet and use the function to pad strings in cells. For example this cell formula:

=PadString(A1,50,"#","left")

The function requires you to provide 4 parameters.

• pString – String to pad
• pLength – Required length of padded string
• pChar – Single character to use for string padding
• pSide – Add padding on the “left” or “right”

If the pLength is less than the length of the pString then the output will be truncated.

Other VBA String Functions

For information on some of the built-in string functions please take a look at the Office VBA Reference.