wrap the text but not any given word

[Red]

The subject line describes what i want to do. I think the issue relates to many versions of Excel, but i'm most interested in Excel 2003. Thanks in advance for any ideas. I have tried various things and researched quite a bit.

 

[Red]

In other words, i want to wrap the words (plural) but never any single word.

 

[Ron Rosenfeld]

The subject line describes what i want to do. I think the issue relates to many versions of Excel, but i'm most interested in Excel 2003. Thanks in advance for any ideas. I have tried various things and researched quite a bit.

I don't understand what you want to do. Please clarify with examples.

 

[douglashoward4]

Okay, here are two examples:

This example wraps
the text but not
any given word.

This exa
mple wraps
the text and
also one
word.

 

[Ron Rosenfeld]

Okay, here are two examples:

This example wraps
the text but not
any given word.

This exa
mple wraps
the text and
also one
word.

I don't recall this being an issue in Excel 2003. At least in 2007, if I enter a phrase longer than the cell, and select "Wrap Text" (in 2003 you would look at Format/Cells/Alignment, the lines would break only at spaces, or at manually entered line-breaks. I'm pretty sure that is how it has worked in the past, also, but I don't have a copy of 2003 on my machine.

 

[Ron Rosenfeld]

Okay, here are two examples:

This example wraps
the text but not
any given word.

This exa
mple wraps
the text and
also one
word.

OK, I see what you mean. You WANT it to wrap at a certain line length, irrespective of whether there is a word break.
You will need to enter manual line-breaks to do that. This can be easily automated with a macro.

 

[Ron Rosenfeld]

Okay, here are two examples:

This example wraps
the text but not
any given word.

This exa
mple wraps
the text and
also one
word.

Here is a macro that will place the line feeds every 10th character
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

The macro assumes you want this done on every entry in column A, (and also assumes there will be at least two entries, else there will be an error).
It can be easily altered for other requirements.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

====================================
Option Explicit
Sub Wrap()
Dim v As Variant
Dim r As Range
Dim i As Long, j As Long
Const LineLength As Long = 10
Set r = Range("a1", Cells(Rows.Count, "A").End(xlUp))
v = r
For i = LBound(v) To UBound(v)
j = 0
Do Until j + LineLength > Len(v(i, 1))
v(i, 1) = WorksheetFunction.Replace(v(i, 1), j + LineLength + 1, 0, vbLf)
j = j + LineLength
Loop
Next i
Set r = r.Offset(columnoffset:=1)
r.EntireColumn.Clear
r = v
End Sub
=================================

 

[Red]

OK, I see what you mean.
You WANT it to wrap at a certain line length, irrespective of whether there is a word break.

Thank you very much for your replies.
I do want the words to break at a certain line length, but never, never, with a word break.

 

[Ron Rosenfeld]

never, never, with a word break.

That is more complicated than just breaking at a given number of characters.

Here's another macro that makes use of Regular Expressions to ensure that the split does NOT occur at a <whitespace> character which is defined as a space, tab or linebreak. (That can be changed if necessary).

Use the same method of entering the macro as before. Note that, as written, it process everything in Column A and places the results in the adjacent cell in Column B

==================================
Option Explicit
Sub BreakNotAtWord()
Dim r As Range
Dim v As Variant
Dim i As Long, J As Long
Dim re As Object, mc As Object, m As Object
Dim sPat As String, s As String
Dim vSplitLines() As String
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
v = r

Set re = CreateObject("vbscript.regexp")
i = Application.InputBox("Maximum Characters per Line: ", Type:=1)
sPat = ".{1," & CStr(i - 1) & "}[\S](?=\S|$)"
With re
.Pattern = sPat
.Global = True
.MultiLine = False
End With

If VarType(v) < vbArray Then
s = v
ReDim v(1 To 1, 1 To 1)
v(1, 1) = s
End If


For i = LBound(v) To UBound(v)
If re.test(v(i, 1)) = True Then
Set mc = re.Execute(v(i, 1))
ReDim vSplitLines(1 To mc.Count)
J = 0
For Each m In mc
J = J + 1
vSplitLines(J) = m
Next m
v(i, 1) = Join(vSplitLines, vbLf)
End If
Next i

Set r = r.Offset(columnoffset:=1)
Application.ScreenUpdating = False
r.EntireColumn.Clear
r = v
r.WrapText = True
r.EntireColumn.ColumnWidth = 255
r.EntireRow.AutoFit
r.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
================================

 

[Red]

Thank you very much for your work below. It might be more than i can deal with and test, but it is likely to help others. Thanks again.