# split cell at space near 20th character

#### serranli

##### New Member
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

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Does this work for you:
=LEFT(A1,LEN(SUBSTITUTE(LEFT(A1,21)," ","")))

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

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 !

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

.. 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!

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

Code:
``````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``````

For a VBA solution, use the UDF
Code:
``````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.

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

Replies
9
Views
276
Replies
9
Views
722
Replies
8
Views
980
Replies
5
Views
275
Replies
11
Views
3K

1,203,174
Messages
6,053,914
Members
444,694
Latest member
JacquiDaly

### 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.

### Which adblocker are you using?

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

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