split cell at space near 20th character

[serranli]

Hi guys,

I am not an excel expert at all.. but I have to succeed in the next task:
I have received an excel file with only text in column A. This means that I have lines of sometimes 100 characters in one cell.

For printing labels on monday, I need to split up this one cell (or this one column) into cells containing maximum 20 characters and always ending with a blank/space or full word (so not cutting words in halfs). Question is: how do I automatically split a cell near the 20th character, pushing the remainder of the text to a next cell/column with again max 20 characters and so on?

I tried with this formula... but really some problems left and I loose all 'remaining text': =LEFT((MID(A1;1;20));LEN((MID(A1;1;20)))-LEN((RIGHT(MID(A1;1;20);FIND(" ";A1)))))
It seemed logical to first tell excel to find me the first 20 characters and then substract everything from the last blank onwards.. but this formula does'nt seem to cover that one ;o)

I hope someone can help here.

Thank you very much for your time and help!
All the best from Belgium (sleepy Belgium...)

 

[tactps]

Does this work for you:
=LEFT(A1,LEN(SUBSTITUTE(LEFT(A1,21)," ","")))

 

[tactps]

And line 2:
=MID(A1,LEN(SUBSTITUTE(LEFT(A1,21)," ",""))+2,20)

 

[serranli]

Tactps, thank you for your help.

If I read and follow your code, it looks logic.. but when I try out with some random sample sentences.. it sometimes hyphens in the middle of a word. As if it is ignoring the search for the blank.. I do not seam to figure it out.. maybe because it is 2 o'clock in the morning. If someone has another idea or can see the mistake, it would be helpful.

Sweet dreams to all of you !

 

[tactps]

Check your format and take the word-wrap off the new cell.

 

[serranli]

.. no format.. no word wrap..

It already looks better with these slight adjustments (I don't 'loose' characters anymore):

=LEFT(A1;LEN(SUBSTITUTE(LEFT(A1;21);"@";"")))
=MID(A1;LEN(SUBSTITUTE(LEFT(A1;21);"@";""));20)


But as you can see, there are still words cut inhalf:

Sample text of more than 20 characters. Sample text of more t than 20 characters.
I really want this to work. I really want this to o work.
Hopefully the answer occurs to me today. Hopefully the answer occurs to me today.

I'd wish I'd had a little more 'excel brains'...

Tx again and have a nice weekend!

 

[jindon]

Hi

UDF, if you like

1) hit Alt + F11 to open VB editor
2) go to Insert -> Module and paste the code onto right pane
3) close the window to get back to Excel
use in cell like

=word20($A1,Column(a1))
then fill to the right

Function word20(txt As String, ref As Integer) As String
Dim x, i, y, result()
Do While Len(txt) > 0
    x = Split(txt): i = 0
    Do While Len(y) + Len(x(i)) <= 20
        y = y & x(i) & Chr(32)
        i = i + 1
        If i > UBound(x) Then Exit Do
    Loop
    n = n + 1
    ReDim Preserve result(1 To n)
    result(n) = Trim(y): txt = Trim(Replace(txt, Trim(y), vbNullString)): y = Empty
    If ref = n Then word20 = result(ref):  Exit Function
Loop
End Function

 

[tusharm]

For a VBA solution, use the UDF

Option Explicit
Option Base 0

Function TwentyOrLess(InString As String)
    Dim Rslt() As String, Tokens, ThisResult As String, I As Integer
    ReDim Rslt(0)
    Tokens = Split(InString, " ")
    ThisResult = ""
    For I = LBound(Tokens) To UBound(Tokens)
        If Len(ThisResult) + Len(Tokens(I)) > (20 - 1) Then
            Rslt(UBound(Rslt)) = ThisResult
            ReDim Preserve Rslt(UBound(Rslt) + 1)
            ThisResult = Tokens(I)
        Else
            ThisResult = ThisResult & " " & Tokens(I)
            End If
        Next I
    If ThisResult <> "" Then
        Rslt(UBound(Rslt)) = ThisResult
    Else
        ReDim Preserve Rslt(UBound(Rslt) - 1)
        End If
    TwentyOrLess = Application.WorksheetFunction.Transpose(Rslt)
    End Function

For a formula based solution use the following:
Suppose you have your token string in A starting with A1 and some number of empty rows between tokens.
Then, in G1 use =MID(A1,1,20) to extract up to 20 characters.
In H1 use =SUBSTITUTE(G1," ",CHAR(255),LEN(G1)-LEN(SUBSTITUTE(G1," ",""))) to replace the last occurence of space in the truncated string with char(255)
Then, in I1 use =IF(LEN(A1)<=20,G1,LEFT(H1,FIND(CHAR(255),H1)-1)) to get the string of no more than 20 characters.

In A2 get the remaining string with =TRIM(MID(A1,LEN(I1)+1,1024)) In G:I copy down the formulas. Copy A:I down as required.

Kinda clumsy, I'd say. One could improve the layout and presentation by putting all the formulas in a single row.

Both approaches lightly tested.

 

[serranli]

split cells near 20th character: terrific, thanks a lot!!

Jindon, Tusharm,

thank you both very much! I have no idea what UDF is, but the most important is: that it works! Jindon, your solution was very easy for me to implement, done in 2 minutes - really great.

Thank you all

Best from Belgium, Serranli