Add limit to a string

Posted on 2011-04-20
Last Modified: 2012-05-11
I am outputting values from a excel spreadsheet, but what I would like to know is how to place constraints on the output for string limits. I need to have the spaces show up in the output as part of the limit because the data in the excel spreadsheet may not be the full max limit, but I need to account for spaces
I read each column as such:
strA = (oSheet.Cells(iRow, 1).Value) 'Transaction Key

and output in this manner:
oOut.WriteLine strA & "" & strB &

I need my text file to look like this..
If string A had a limit of 10, but it had 3 characters the output should be "123       ".  

Thank you
Question by:drezner7
    LVL 16

    Accepted Solution

    Depending on where/how your string limits are defined, the quickest way would be to do:

        strA = Left((oSheet.Cells(iRow, 1).Value) & Space(theLimit), theLimit)

    where theLimit was the limit defined for column #1.


    LVL 16

    Expert Comment

    (although 'quickest' is probably not the best term, here; simplest might be better...)

    To clarify, the command Space(theLimit) returns a string of theLimit spaces, which is appended to the end of the cell value, then the whole thing is truncated from the left, up to theLimit characters, by the wrapping Left(..., theLimit) call.

    This assumes that the limit of the string is a maximum length and, if the cell value exceeds this, it should be truncated.  If the limit is only really a minimum length, you might use:

        strA = (oSheet.Cells(iRow, 1).Value)
        If Len(strA) < theLimit Then strA = Left(strA & Space(theLimit), theLimit), strA)


    Author Comment

    Ok, so if the limit was 10 then it would look like this correct?

    strA = Left((oSheet.Cells(iRow, 1).Value)& Space(10),10 )

    Author Closing Comment

    Worked like a charm....

    Thank you very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now