My question is, if your text line is *really* long and spills over the page margin, how do you make it fit on the page (in two or three lines of text) without wrapping all the text into one very tall cell (i.e. a narrow column)? I haven’t been able to find out how to do this and it’s driving me crazy. Any ideas? Thanks, and since I am a beginner, I need very specific info.
I think Jenny wants Edit > Fill > Justify, like this
You may note the problem with this feature is that it only works on the first 255 characters of the text. I have my own Justify procedure, but it differs from the built in function because it doesn’t alert you when your text is going to extend beyond the selection. I tried to modify it so I could post it here, but I had problems with it.
Dim sContents As String
Dim lMaxLen As Long
Dim rCell As Range
Dim i As Long
Dim lCut As Long
Dim lResp As Long
If TypeName(Selection) = “Range” Then
If Not IsEmpty(Selection.Cells(1).Value) Then
sContents = Selection.Cells(1).Text
Application.DisplayAlerts = False
For Each rCell In Selection.Columns(1).Cells
If Len(rCell.Text) > lMaxLen Then
lMaxLen = Len(rCell.Text)
Application.DisplayAlerts = True
lResp = vbOK
If Len(sContents) / lMaxLen > Selection.Rows.Count Then
lResp = MsgBox(“Text will extend below the selected range.”, vbExclamation + vbOKCancel)
If lResp = vbOK Then
Do Until Len(sContents) < lMaxLen
i = i + 1
lCut = InStrRev(sContents, Chr$(32), lMaxLen, vbTextCompare)
If lCut = 0 Then
lCut = lMaxLen
Selection.Columns(1).Cells(i).Value = Left$(sContents, lCut)
sContents = Right$(sContents, Len(sContents) – lCut)
Selection.Columns(1).Cells(i + 1).Value = sContents
Selection.Cells(1).Value = sContents
I use the built in Justify method to determine what the maximum length of the line should be. It’s not perfect, but it seemed like an easy way to find where to split the text. I surround it with DisplayAlerts properties because I need to control the message about text that’s too long. Because I’m not truncating at 255 characters, what I consider too long and what Excel does may be different.
The problem that I have is that the built in Justify might overwrite data before the user has a chance to cancel. Even if it doesn’t overwrite data, if the user cancels it still may leave an orphan sentence or two. To see what I mean, put a sufficiently long string in A1, select A1:C3, then click Cancel when prompted.
I need a better way to find lMaxLen than the Justify method.