Split cell without breaking word?

[Doug Benjamin]

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

[Toppers]

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

"Doug Benjamin" wrote:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[Therese]

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
--
Therese


"Doug Benjamin" skrev:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[Therese]

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


"Doug Benjamin" skrev:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[Doug Benjamin]

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

Thanks...Doug

[Toppers]

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
(toppers@johntopley.fsnet.co.uk)

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

"Doug Benjamin" wrote:

>
> Toppers:
> How do I execute that fomulation? I am using Excel 2000. Will I need
> 2003 to do that?
>
> Thanks...Doug
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[Therese]

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


"Doug Benjamin" skrev:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[JMB]

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))


"Doug Benjamin" wrote:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>

[JMB]

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)))

"Doug Benjamin" wrote:

>
> What I want to do is split up a cell of about 25-60 characters into two
> cells without ending up with words cut in half or duplicated words. I
> want the first 30 characters in the first cell and the rest if any in
> the second cell. Any Ideas?
>
>
> --
> Doug Benjamin
> ------------------------------------------------------------------------
> Doug Benjamin's Profile: http://www.excelforum.com/member.php...o&userid=33407
> View this thread: http://www.excelforum.com/showthread...hreadid=532285
>
>