-
Break down a string
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
-
Hi Paul
Have a look at this
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thanks, i ll have a play tomorrow
-
Here's a different approach, using the Split function.
[vba]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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules