# Split Cell depending on characters and spaces

#### joz3

##### New Member
Hi, i have groups of data and If the string is longer than 40 characters, I need the text before and after split into seperate columns but at the last space before the 40 character limit.

For Example:

String:This is the long string that i want splitting into two columns

Result1:This is the long string that i want
Result2:splitting into two columns

If i split at 40 characters i would split a word so want it doing before this occurs.

Thanks for any help.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Weaver

##### Well-known Member
If your text is in A1

B1: =TRIM(LEFT(A1,FIND("%%",SUBSTITUTE(A1," ","%%",40-LEN(SUBSTITUTE(LEFT(A1,40)," ",""))))))
C1: =TRIM(SUBSTITUTE(A1,B1,""))

PS I used %% as a splitter on the basis you normally wouldn't find 2 of these together in a sentence. If any of your text does, change the formula to something that definitely won't appear

HTH

#### Weaver

##### Well-known Member
PS this won't help much if the second part is > 40 characters, but you could reapply the formula to the result of c1 if you're worried about this. Possibly not the ideal solution tho....

If you're wondering, it counts how many spaces there are in the first 40 characters, substitutes the last one for the %% then splits & trims the text at this point.

#### tryagain

##### Board Regular
Sub SplitText()
x = Trim(Selection)
For t = 1 To Abs(Len(x) / 40) + 1
Selection.Offset(0, t) = Left(x, InStrRev(Left(x, 40), " "))
x = Replace(x, Selection.Offset(0, t), "")
If Len(x) < 41 Then Exit For
Next
Selection.Offset(0, t + 1) = x
End Sub

#### AYR_RAY

##### New Member
I am in love with your below macro which takes a given string and converts it into 3 to 4 coloums of chrs not exceeding 40 chrs ,
i want to use it in a excel sheeet where i have 2300 adress to split , address lenghts are from 40 to 120 chrs
can you change the code so i can use it as a function , i.e splitetext(a1) if my data is in a1 and the slit data would appear in c1 , d1 , e1, and so on

Sub SplitText()
x = Trim(Selection)
For t = 1 To Abs(Len(x) / 40) + 1
Selection.Offset(0, t) = Left(x, InStrRev(Left(x, 40), " "))
x = Replace(x, Selection.Offset(0, t), "")
If Len(x) < 41 Then Exit For
Next
Selection.Offset(0, t + 1) = x
End Sub

#### Rick Rothstein

##### MrExcel MVP
Here is a UDF (user defined function) that will do what you want...

Code:
``````Function WrapText(CellWithText As String, MaxChars, FieldNumber As Long) As String
Dim Space As Long, Text As String, TextMax As String, Parts() As String
Text = CellWithText
Do While Len(Text) > MaxChars
TextMax = Left(Text, MaxChars + 1)
If Right(TextMax, 1) = " " Then
WrapText = WrapText & RTrim(TextMax) & vbLf
Text = Mid(Text, MaxChars + 2)
Else
Space = InStrRev(TextMax, " ")
If Space = 0 Then
WrapText = WrapText & Left(Text, MaxChars) & vbLf
Text = Mid(Text, MaxChars + 1)
Else
WrapText = WrapText & Left(TextMax, Space - 1) & vbLf
Text = Mid(Text, Space + 1)
End If
End If
Loop
Text = WrapText & Text
Parts = Split(Text, vbLf)
If FieldNumber - 1 <= UBound(Parts) Then
WrapText = Parts(FieldNumber - 1)
Else
WrapText = ""
End If
End Function``````

To use this UDF... the first argument is the cell containing your text, the second argument is the maximum number of characters you wan per cell, and use COLUMN(A1) as the field counter (3rd argument) in the first formula and it will iterate as 1, 2 , 3, etc. for each column it is copied across to. So, your first formula, in whatever cell you want, would be...

=WrapText(\$A1,40,COLUMN(A1))

and you would copy it across for as many cells as you think you will ever need.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. See above for an example of how it is used. If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Replies
9
Views
211
Replies
4
Views
104
Replies
0
Views
178
Replies
9
Views
128
Replies
8
Views
600

1,181,921
Messages
5,932,805
Members
436,862
Latest member
OhioExcelGuy

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

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