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

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

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

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

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

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
)

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

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

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

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