Here is a sub that will do it:
Public Sub FortyCharSplit(InputText As String, OutputCells As Range)
Dim Cell1Text As String, Cell2Text As String, LastSpace As Integer
Cell1Text = Left(InputText, 40)
Cell2Text = Right(InputText, Len(InputText) - 40)
' Test to see if the split is already at a space; if not find one:
If Not ((Mid(InputText, 40, 1)) = " " Or (Mid(InputText, 41, 1) = " ")) Then
' Find the last space in the first cell text:
LastSpace = InStrRev(Cell1Text, " ")
' We have to deal with the (unlikely) chance that there are NO spaces:
If LastSpace < 0 Then
Cell2Text = Right(Cell1Text, Len(Cell1Text) - LastSpace) & Cell2Text
Cell1Text = Left(Cell1Text, LastSpace)
End If
End If
' clean up any leading or trailing spaces:
OutputCells.Cells(1, 1) = "'" & Trim(Cell1Text)
OutputCells.Cells(1, 2) = "'" & Trim(Cell2Text)
End Sub

The sub is set up so that the first cell will always be <40 characters, even
if it makes the second one 40; also, if there are no spaces within the
first 41 characters or more (unlikely I hope) then you will end up with a
"forced" split at 40 characters anyway.
--
- K Dales


"1scant" wrote:


I have to split an 80 character text string into two 40 character
fields. But it needs to be done like line wrap, not splitting a word.
I am not too concerned with the second field running over the 40
character.

Can someone help this Excel VBA newbie?

Thx


--
1scant
------------------------------------------------------------------------
1scant's Profile: http://www.excelforum.com/member.php...o&userid=31635
View this thread: http://www.excelforum.com/showthread...hreadid=513277