Hi Tom,
Thank you for the help I now just need to test it. When you mention wordwrap
do you meant by that I have to change the column to text in the excel file
before I do the program manipulation, or...? Sorry, but as I wrote I am
novice using VB.
JIP
"Tom Ogilvy" skrev i en meddelelse
...
Sub AABB()
Dim rng As Range, cell As Range
Dim sStr As String, sSTr1 As String
Dim i As Long
Set rng = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))

For Each cell In rng
' remove trim if you don't want leading and
' trailing spaces removed
sStr = Trim(cell.Value)
If Len(sStr) 40 Then
sSTr1 = Left(sStr, 40)
i = 40
Do While Mid(sSTr1, i, 1) < " " And i 0
i = i - 1
Loop
If i < 2 Then
cell.Interior.ColorIndex = 3
Else
sStr = Left(sStr, i - 1) & vbLf & Right(sStr, Len(sStr) - i)
cell.Value = sStr
End If
End If
Next
End Sub

Test on a copy of your workbook/data. You will need to format the column
to
wordwrap.

--
Regards,
Tom Ogilvy


"jip" wrote in message
...
Platform: Windows 2000/Excel 2000


Is there anybody out here who is able to help me solving following
Excel-case, I am a novice using VB therefore I hope you are a little
passion
with me:

I have an Excel-file consists of approximately 56000 rows and 10
columns,
one of these columns (lets say column E) contain a description of
characters
from 0 to 80.

I need Visual-basic to go through each cell in column E and divide the
text
into two "lines" first "line" can maximum consist of 40 characters the
second "line" has no maximum. My problem is that the text often will be
divided in middle of a word that I do not want.
Therefore I imagine the program first check on character 40 if it is a
"space" or not. If it is a space nothing has to be done opposite the
program
must go back through the text from character 40 until it meet a space.
Afterwards it shall put in "spaces" from the found space up to
characters
40.

That is all what I need.

JIP