Splitting a string out in a cell into 60 character chunks

[laundon]

Hello!

I have a problem that I would like help solving if I may. I have a spreadsheet that has a cell containing a string of text. I need to split this cell into 60 character chunks, preserving whole words if at all possible. The maximum cell length I have is 240 characters so I'll need to split my single cell into up to 4 separate cells of 60 characters.

Am I asking the impossible, or is this doable in Excel?

Any advice/help greatly appreciated!

Kind Regards,
Tim.

 

[Trebor76]

Re: Help splitting a string out in a cell into 60 character chunks

Hi Tim,

Assuming the data is in cell a2 (change the following formulas to suit) put these formulas in whatever cells you want the each result:

=LEFT(A2,60)
=MID(A2,61,60)
=MID(A2,121,60)
=MID(A2,181,60)

HTH

Robert

 

[laundon]

Re: Help splitting a string out in a cell into 60 character chunks

Hi Robert,

Thanks for that. It almost does what I need. It splits the field out into the 60 char parts which is great, that's a better result than I was getting! However, I need to be able to recognise words if possible so that they are not split over a cell and it word wraps them if at the end of 60 characters, if it is half way through a word, that word is dumped into the next cell if that makes sense?

Cheers,
Tim

 

[Trebor76]

Re: Help splitting a string out in a cell into 60 character chunks

I think so. Sounds like you need a VBA solution. Someone here should be able to provide you with some code.

 

[footoo]

Re: Help splitting a string out in a cell into 60 character chunks

Not tested :

Sub v()
Dim ray() As String, i%, ns$, x%
ray = Split([A1], " ")
For i = 0 To UBound(ray)
    If Len(ns) + Len(ray(i)) + 1 < 60 Then
        ns = ns & " " & ray(i)
    Else
        [B1].Offset(x).Value = ns
        ns = ray(i)
        x = x + 1
    End If
Next
[B1].Offset(x) = ns
End Sub

 

[footoo]

Re: Help splitting a string out in a cell into 60 character chunks

Revised :

Sub v()
Dim ray() As String, i%, ns$, x%
ray = Split([A1], " ")
For i = 0 To UBound(ray)
    If Len(ns) + Len(ray(i)) + 1 < 60 Then
        ns = ns & " " & ray(i)
    Else
        [B1].Offset(x).Value = [COLOR=#ff0000]Trim(ns)[/COLOR]
        ns = ray(i)
        x = x + 1
    End If
Next
[B1].Offset(x) = [COLOR=#ff0000]Trim(ns)[/COLOR]
End Sub

 

[AlKey]

Re: Help splitting a string out in a cell into 60 character chunks

Here is a formula solution
Assuming your text string is maximum 240 characters and located at cell A1
1. Enter formula in B1

=LEFT(A1,LOOKUP(60,FIND(" ",A1&" ",ROW(INDIRECT("1:"&LEN(A1)))-1)))

2. Enter formula in A2 and copy 4 rows down

=SUBSTITUTE(A1,B1,"")
then drag formula in B1 down
This formula will split text string by 60 or less characters without breaking words.

 

[footoo]

Re: Help splitting a string out in a cell into 60 character chunks

Here's an improved version.
First select the cell containing the text then run the macro.
The split will be put in the column next to the cell.

Sub v()
Dim ray() As String, i%, ns$, x%
If Selection.Count > 1 Then
    MsgBox "select one cell only."
    Exit Sub
End If
ray = Split(WorksheetFunction.Trim(Selection), " ")
ns = ray(0)
For i = 1 To UBound(ray)
    If Len(ns) + Len(ray(i)) + 1 < 60 Then
        ns = ns & " " & ray(i)
    Else
        Selection.Offset(x, 1).Value = ns
        ns = ray(i)
        x = x + 1
    End If
Next
Selection.Offset(x, 1) = ns
End Sub

Note : Depending upon the total characters and whether words fit exactly into 60 character blocks, the split may consist of 5 rows.

 

[rajeshrikant]

Re: Help splitting a string out in a cell into 60 character chunks

Here's an improved version.
First select the cell containing the text then run the macro.
The split will be put in the column next to the cell.

Sub v()
Dim ray() As String, i%, ns$, x%
If Selection.Count > 1 Then
    MsgBox "select one cell only."
    Exit Sub
End If
ray = Split(WorksheetFunction.Trim(Selection), " ")
ns = ray(0)
For i = 1 To UBound(ray)
    If Len(ns) + Len(ray(i)) + 1 < 60 Then
        ns = ns & " " & ray(i)
    Else
        Selection.Offset(x, 1).Value = ns
        ns = ray(i)
        x = x + 1
    End If
Next
Selection.Offset(x, 1) = ns
End Sub

Note : Depending upon the total characters and whether words fit exactly into 60 character blocks, the split may consist of 5 rows.

Hi,
This works well and splits address in the rows. I need the split data to appear in the columns. Can you please suggest how it can be done? Thanks in advance.

 

[footoo]

Sub v()
Dim ray() As String, i%, ns$, x%
If Selection.Count > 1 Then
    MsgBox "select one cell only."
    Exit Sub
End If
ray = Split(WorksheetFunction.Trim(Selection), " ")
ns = ray(0)
x = 1
For i = 1 To UBound(ray)
    If Len(ns) + Len(ray(i)) + 1 < 60 Then
        ns = ns & " " & ray(i)
    Else
        Selection.Offset(0, x).Value = ns
        ns = ray(i)
        x = x + 1
    End If
Next
Selection.Offset(0, x) = ns
End Sub