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.
