Split Cell depending on characters and spaces

joz3

New Member
Joined
Aug 17, 2009
Messages
13
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.
 

Some videos you may like

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
Joined
Sep 10, 2008
Messages
5,196
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
Joined
Sep 10, 2008
Messages
5,196
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
Joined
Mar 15, 2010
Messages
102
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
Joined
Dec 25, 2013
Messages
2
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
please help my email id is afzal.reshamwala@gmail.com


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
Joined
Apr 18, 2011
Messages
36,200
Office Version
2010
Platform
Windows
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top