Facebook Twitter Instagram
    Trending
    • Hard Drive Repair After Wrong Voltage Mishap
    • How To Send Excel Workbook As Email Using Button
    • Compare EV Chargers
    • Home Assistant Server Monitoring
    • Mitsubishi Outlander Dash Cam Install
    • Left or Right Pad a String with a VBScript Function
    • List Files in a Folder Using VBScript
    • How to Verify Checksum of a File
    RSS Twitter Instagram Facebook Pinterest YouTube
    Technology Spy
    • Tutorials & Help
    • Reviews
    Technology Spy
    You are at:Home»Tutorials & Help»Left or Right Pad a String with a VBScript Function
    Visual Basic Applications

    Left or Right Pad a String with a VBScript Function

    0
    By Matt on January 21, 2021 Tutorials & Help, VBScript

    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.

    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email
    Previous ArticleList Files in a Folder Using VBScript
    Next Article Mitsubishi Outlander Dash Cam Install

    Related Posts

    List Files in a Folder Using VBScript

    How to Verify Checksum of a File

    Lenovo T460 Trackpad Ubuntu 18.04 Fixes

    Leave A Reply Cancel Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Recent Posts
    November 20, 2022

    Hard Drive Repair After Wrong Voltage Mishap

    March 22, 2022

    How To Send Excel Workbook As Email Using Button

    January 30, 2022

    Compare EV Chargers

    Categories
    • 3D Printing
    • Android
    • Arduino
    • Development
    • ESP8266
    • Excel
    • Gaming
    • General
    • GIMP
    • Home Automation
    • JavaScript
    • Linux
    • Microsoft Office
    • Mobile Devices
    • Oracle APEX
    • Python
    • Raspberry Pi
    • Reviews
    • Security
    • SharePoint
    • Tutorials & Help
    • VBScript
    Tags
    Android APEX Apple Arduino Black Friday csv Elite Dangerous email ESP-01 ESP8266 EV Excel file handling gaming GIMP Gritin Home Assistant Home Automation IMNEED Joystick Linux lists Lumsing Media os.stat os.walk Phishing Power Python Qi Review scam Security select SharePoint 2010 string Syncwire text text files TrueCrypt Ubuntu USB-C VBA VBscript Windows 10
    Web Tools

    A set of quick and basic online tools for web designers and software developers :

    • Hash Generator
    • Text to HTML List
    • Text to HTML Table
    • URL Encoder and Decoder
    • UNIX Timestamp Calculator
    • LED Resistor Calculator
    • Extract Email from Text
    • Mortgage Calculator
    About

    Welcome to Technology Spy, a site devoted to gadgets, computers, programming and all things technology! You’ll also find product reviews for items I own as well as tutorials, guides and scripts for the software I use.

    Archives
    Other Resources
    • MattsBits
    • Raspberry Pi Spy
    Facebook Twitter Instagram Pinterest

    Entries RSS | Comments RSS

    Copyright © 2022 - All Rights Reserved - Matt Hawkins

    mastodon.social@MattHawkins

    Type above and press Enter to search. Press Esc to cancel.