Split cell without breaking word?

  • Thread starter Thread starter Doug Benjamin
  • Start date Start date
D

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
 
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
 
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
 
Toppers:
How do I execute that fomulation? I am using Excel 2000. Will I need
2003 to do that?
:confused:
Thanks...Doug
 
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
 
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))
 
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)))
 
Back
Top