Break down a string

[gibbo1715]

Hi All been a while

I have a string that can be a pretty long value

what i need to do is if the string is mnore than 40 chars long add a line break

eg

this is my initial string which is in cell A1 and it goes on and on and on

I need to put it in cell a2 as below

this is my initial string which is in cell A1
and it goes on and on and on

thanks all

Paul

[mdmackillop]

Hi Paul
Have a look at this (http://vbaexpress.com/kb/getarticle.php?kb_id=481)

[gibbo1715]

Thanks, i ll have a play tomorrow

[mdmackillop]

Here's a different approach, using the Split function.

Sub FixText()
'Call the sub with Text location, Address of target cell, Length of fragments
DoSplit Range("A1"), Range("A3"), 40
End Sub

Sub DoSplit(Txt As String, Tgt As Range, Lgth As Long)
Dim Txt1 As String
Dim i As Long, Len1 As Long, TxtLen As Long

Do
'Get original/remaining text length
TxtLen = Len(Txt)
'Take the first set of characters
Txt1 = Left(Txt, Lgth)
'Find the length up to the last space
Len1 = Lgth - Len(Split(Txt1, " ")(UBound(Split(Txt1, " "))))
'Exclude text after the last space
Txt1 = Left(Txt, Len1)
'Write the extracted text to a cell
Tgt.Offset(i) = Txt1
'Exit if no more text to be processed
If Len1 > TxtLen Then Exit Do
'Loop with the remaining text
Txt = Trim(Right(Txt, TxtLen - Len1))
i = i + 1
Loop
End Sub