split cell at space near 20th character


New Member
Nov 4, 2005
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...)

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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 !
Upvote 0
.. no format.. no word wrap..

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


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!
Upvote 0

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

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
    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
End Function
Upvote 0
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)
            ThisResult = ThisResult & " " & Tokens(I)
            End If
        Next I
    If ThisResult <> "" Then
        Rslt(UBound(Rslt)) = ThisResult
        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.
Upvote 0
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back