Split Cell depending on characters and spaces

[joz3]

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.

 

[Weaver]

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]

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]

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]

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]

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

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.