Split cell without breaking word?

[Doug Benjamin]

What I want to do is split up a cell of about 25-60 characters into tw
cells without ending up with words cut in half or duplicated words.
want the first 30 characters in the first cell and the rest if any i
the second cell. Any Ideas

 

[Guest]

Assuming text is words separated by blanks:

Dim v As Variant
Dim str1 As String, str2 As String
Dim fstr As Boolean
v = Split(Textstring," ")
str1 = ""
str2 = ""
fstr = True
For i = LBound(v) To UBound(v)
If Len(str1) + Len(v(i)) > 30 Then fstr = False
If fstr Then
str1 = str1 + v(i) + " "
Else
str2 = str2 + v(i) + " "
End If
Next i
MsgBox str1 & " / " & str2

 

[Guest]

Hi
One suggestion, you can doubleclick on the edge of the column to make it
automatically fitt, and then manually split the text.
You can also mark the sheet (ctrl+ A) and then format<cells< adjust and then
"split text". Sorry, danish...don't know what it says in the english verson.
If it's only one cell don't mark the whole sheet but format<cells< etc.
Hope it's what you meant.
THANKS

 

[Guest]

UUuhh...sorry about the Thanks-caps. Ooops!!!:0)

 

[Doug Benjamin]

Toppers:
How do I execute that fomulation? I am using Excel 2000. Will I need
2003 to do that?

Thanks...Doug

 

[Guest]

This is VBA (Visual Basic for Applications) code.

As you are not familar with this you may need to do some research - I was
assuming some knowledge when I replied. Some addiional code will be required
as I expect you have many cells to convert.

To save time, send me your w/book anf I'll incorporate the code
([email protected])

HTH


Sub A
Dim v As Variant
Dim str1 As String, str2 As String
Dim fstr As Boolean
v = Split(Textstring," ")
str1 = ""
str2 = ""
fstr = True
For i = LBound(v) To UBound(v)
If Len(str1) + Len(v(i)) > 30 Then fstr = False
If fstr Then
str1 = str1 + v(i) + " "
Else
str2 = str2 + v(i) + " "
End If
Next i
MsgBox str1 & " / " & str2
End sub

 

[Guest]

Hi
If it's too complicated, there is a formula that can do it. What caracters
are they?

 

[Guest]

Another suggestion. Assuming your string is in cell A1, enter this in B1 to
get the first 30 characters (without splitting whole words - this must be
entered as an array formula which means it must be confirmed
w/Control+Shift+Enter):

IF(LEN(A1)<=30,A1,LEFT(A1,MATCH(30,IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "), (ROW(INDIRECT("1:"&LEN(A1)))),""),1)))

Then enter this in C1 to return the rest of the string:
=RIGHT(A1,LEN(A1)-LEN(B1))

 

[Guest]

One other suggestion if you don't want any leading/trailing spaces in the
split data

Array Entered (and changed slightly due to nesting limits):
=TRIM(IF(LEN(A1)<=30,A1,LEFT(A1,LARGE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")* (ROW(INDIRECT("1:"&LEN(A1)))<=30)*(ROW(INDIRECT("1:"&LEN(A1)))),1))))

Entered Normally:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))