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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. 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.
 

Forum statistics

Threads
1,144,578
Messages
5,725,095
Members
422,590
Latest member
Mikeyyy

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?

Disable AdBlock

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
Top