# 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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

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

1,106,298
Messages
5,510,468
Members
408,791
Latest member
bwirth

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...