software

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: www.excelforum.com/member.php...oamp;userid=33407
View this thread: www.excelforum.com/showthread...hreadid=532285Assuming text is words separated by blanks:

Dim v As Variant
Dim str1 As String, str2 As String
Dim fstr As Boolean
v = Split(Textstring,quot; quot;)
str1 = quot;quot;
str2 = quot;quot;
fstr = True
For i = LBound(v) To UBound(v)
If Len(str1) Len(v(i)) gt; 30 Then fstr = False
If fstr Then
str1 = str1 v(i) quot; quot;
Else
str2 = str2 v(i) quot; quot;
End If
Next i
MsgBox str1 amp; quot; / quot; amp; str2

quot;Doug Benjaminquot; wrote:

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

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 formatlt;cellslt; adjust and then
quot;split textquot;. 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 formatlt;cellslt; etc.
Hope it's what you meant.
THANKS
--
Theresequot;Doug Benjaminquot; skrev:

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

UUuhh...sorry about the Thanks-caps. Ooops!!!:0)
--
Theresequot;Doug Benjaminquot; skrev:

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


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: www.excelforum.com/member.php...oamp;userid=33407
View this thread: www.excelforum.com/showthread...hreadid=532285This 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
)

HTHSub A
Dim v As Variant
Dim str1 As String, str2 As String
Dim fstr As Boolean
v = Split(Textstring,quot; quot;)
str1 = quot;quot;
str2 = quot;quot;
fstr = True
For i = LBound(v) To UBound(v)
If Len(str1) Len(v(i)) gt; 30 Then fstr = False
If fstr Then
str1 = str1 v(i) quot; quot;
Else
str2 = str2 v(i) quot; quot;
End If
Next i
MsgBox str1 amp; quot; / quot; amp; str2
End sub

quot;Doug Benjaminquot; wrote:

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

Hi
If it's too complicated, there is a formula that can do it. What caracters
are they?
--
Theresequot;Doug Benjaminquot; skrev:

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

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)lt;=30,A1,LEFT(A1,MATCH(30,IF((MID(A1,ROW( INDIRECT(quot;1:quot;amp;LEN(A1))),1)=quot; quot;), (ROW(INDIRECT(quot;1:quot;amp;LEN(A1)))),quot;quot;),1)))

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

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

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)lt;=30,A1,LEFT(A1,LARGE((MID(A1,ROW( INDIRECT(quot;1:quot;amp;LEN(A1))),1)=quot; quot;)* (ROW(INDIRECT(quot;1:quot;amp;LEN(A1)))lt;=30)*(ROW(INDIRECT(quot;1 :quot;amp;LEN(A1)))),1))))

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

quot;Doug Benjaminquot; wrote:

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