wrap long string on multiple lines depending on number of characters

Posted on 2008-10-07
Medium Priority
Last Modified: 2008-10-08

A query returns a long string from a database. I need to be able to take this long string - call it strTitle - and perform the following:

if the string is longer than 45 characters, go to the previous blank space (that is, the blank preceeding the 45th character), insert vbNewLine & 3 tab characters (chr(9)), and then continue the string. On this second line, perform the same test as on the first (that is, check that it does not exceed 45 characters, and if it does, back up to the nearest blank space, insert vbNewLine & 3 tab characters (chr(9)), and then continue the string.) Continue this process until the end of the string.

Question by:crafuse
LVL 96

Expert Comment

by:Bob Learned
ID: 22660066
It might be useful to know where and how you are displaying this?  GDI+ has ways to handle word wrapping for you automatically with a layout Rectangle and StringFormat.

Author Comment

ID: 22660166
TheLearnedOne -

I'm simply doing a replace of a bookmark in Word, and the string needs to be formatted in this manner.

This is my call to the replace function:

RangeReplace(docClient, "Titinv", RemoveSpaces(strtitle)) 'RemoveSpaces removes any blanks exceeding one character

And this is how I do the actual replace:

Private Sub RangeReplace(ByRef doc As Object, ByVal strBookmark As String, ByVal strValue As String, Optional ByVal blnMakeBold As Boolean = False)

        Dim wordRange As Object
        Dim wordBookmark As Object
        wordBookmark = doc.Bookmarks(strBookmark)
        wordRange = wordBookmark.Range
        wordRange.Text = strValue
        If blnMakeBold Then wordRange.Bold = True
        wordRange = Nothing
        wordBookmark = Nothing

    End Sub

So you see, I just need a function that behaves (I hope) the way I've described.

crafuse (somewhat unlearned!)
LVL 96

Expert Comment

by:Bob Learned
ID: 22660941
Word automation is a completely different beast.  Are you looking to wrap before inserting text into the document?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 22661023
Yes, so that the function would return someling like

RangeReplace(docClient, "Titinv", (substrTitinv1 & chr(10) & chr(9) & chr(9) & chr(9) & subStrTitinv2 & chr(10) & chr(9) & chr(9) & chr(9) etcetera)

Where the substrTitinv* are the cut up peices of the string. Does this make sense?

LVL 83

Accepted Solution

CodeCruiser earned 2000 total points
ID: 22670664
The following code works perfectly for me. I wrote this demo specifically for you.
        Dim str As String = "Please do not respond to this comment; we will be monitoring your question for activity from the Experts.  If you think that your question may be applicable in zones other than those listed above, please make a post to that effect in the Community Support question linked at the bottom of this comment."
        Dim str2 As New System.Text.StringBuilder
        Dim start = 0, index As Integer
        For i As Integer = 44 To str.Length - 1 Step 45
            index = str.LastIndexOf(" ", i)
            str2.Append(str.Substring(start, index - start) & "(Start:" & start & ", Index:" & index & vbCrLf & Chr(9) & Chr(9) & Chr(9))
            start = index
        str2.Append(str.Substring(start, str.Length - start))

Open in new window


Author Comment

ID: 22671416
CodeCruiser -

Don't know what to say - that's better than awesome. But I'll leave it at that.
I made a couple of minor modifications - had to increment start = index + 1 to manage the blank on which the string chopped, and then had to make only a minor change to the actual bookmark's location, and now it works great. Many thanks!

Function FormatTitle(ByVal sSrc As String) As String

        Dim str2 As New System.Text.StringBuilder
        Dim start = 0, index As Integer
        For i As Integer = 34 To sSrc.Length - 1 Step 35
            index = sSrc.LastIndexOf(" ", i)
            'str2.Append(sSrc.Substring(start, index - start) & "(Start:" & start & ", Index:" & index & vbCrLf & Chr(9) & Chr(9) & Chr(9))
            str2.Append(LTrim(sSrc.Substring(start, index - start) & vbCrLf & Chr(9) & Chr(9) & Chr(9)))
            start = index + 1
        str2.Append(sSrc.Substring(start, sSrc.Length - start))
        FormatTitle = str2.ToString

    End Function
LVL 14

Expert Comment

ID: 22675764
Clever solution. I would recommend the following minor change to improve readability:

str2.AppendFormat("{0}{1}{2}{2}{2}",  sSrc.Substring(start, index - start).TrimStart(), vbCrLf, Chr(9))

If you are not familiar with the AppendFormat method of the StringBuilder (or the .Format method of the string object), you specify indexed placeholders in the string using the curly braces, and then specify the values that go into the placeholders. The 1st value in the list (or array with index 0) goes into placeholder 0, the next one into 1 etc....

Personally, I like this method a LOT better than concatinating strings using the "&" operator and all the extra quotes.

For example, what do you think is easier to read?

Dim strX As String = "The value '" & CurrentValue & "' is not valid the " & Operation & " operation."


Dim strX As String = String.Format("The value '{0}' is not valid for the {1} operation.", CurrentValue, Operation)

Both of the lines above will result in an eqivalent value in the variable strX.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

607 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