Text-to-Column after every 100 words

[mlarson]

Hi there, this is probably a somewhat strange request, but I’d like to break up one cell into multiple cells based on the number of words in that original cell.

So, for example, if I want to break a cell after every 100 words, how would I do that?

I’m guessing there’s a way to use the delimited in text-to-column, but not sure how to set that at 100 words. Any help would be appreciated. Thank you!

 

[Joe4]

I don't think you can use Text-to-Columns for this.

You have two options for splitting up data in Text-to-Columns.
1. Space delimited, where you tell it EXACTLY when to split each record (i.e. at the 100th character)
2. Character delimited, where you can split each field on the existence of some character, i.e. a comma, semi-colon, space, tab, etc.
And whatever rule you choose is applied for ALL the data rows.

So I don't think Text-to-Columns is going to work for you.
The only thing I can think of is to use VBA, looping through each row of data, and counting the spaces which separate your words, and split after the 100th one, and then continue on and repeat.

 

[veyselemre]

Hi, try with VBA

Sub test()
    Dim i&, cell As Range, bl, son&, say%, ii%, yMet$
    For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(3).Row)
        bl = Split(cell.Value, " ")
        If UBound(bl) > 99 Then
            say = 1
            For i = 0 To UBound(bl) Step 100
                son = IIf(i + 99 > UBound(bl), UBound(bl), i + 99)
                yMet = ""
                For ii = i To son
                    yMet = yMet & " " & bl(ii)
                Next ii
                cell.Offset(, say).Value = Trim(yMet)
                say = say + 1
            Next i
        Else
            cell.Offset(, 1).Value = cell.Value
        End If
    Next cell
End Sub

 

[mlarson]

Hi, try with VBA

Sub test()
    Dim i&, cell As Range, bl, son&, say%, ii%, yMet$
    For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(3).Row)
        bl = Split(cell.Value, " ")
        If UBound(bl) > 99 Then
            say = 1
            For i = 0 To UBound(bl) Step 100
                son = IIf(i + 99 > UBound(bl), UBound(bl), i + 99)
                yMet = ""
                For ii = i To son
                    yMet = yMet & " " & bl(ii)
                Next ii
                cell.Offset(, say).Value = Trim(yMet)
                say = say + 1
            Next i
        Else
            cell.Offset(, 1).Value = cell.Value
        End If
    Next cell
End Sub

This worked, thank you!

 

[mlarson]

I don't think you can use Text-to-Columns for this.

You have two options for splitting up data in Text-to-Columns.
1. Space delimited, where you tell it EXACTLY when to split each record (i.e. at the 100th character)
2. Character delimited, where you can split each field on the existence of some character, i.e. a comma, semi-colon, space, tab, etc.
And whatever rule you choose is applied for ALL the data rows.

So I don't think Text-to-Columns is going to work for you.
The only thing I can think of is to use VBA, looping through each row of data, and counting the spaces which separate your words, and split after the 100th one, and then continue on and repeat.

Thank you, Joe4. The VBA in the thread worked well, but it's good to know those manual options as well in case I need/want to go that route at some point in the future. Thanks!

 

[kvsrinivasamurthy]

Try this macro code.

Sub Split100Words()
Dim Cel As Range
Dim M, temp$, T&, X&
For Each Cel In Range("A2:A10")
If Cel <> "" Then
M = Split(Cel, " ")
    For T = 1 To UBound(M) + 1
    temp = temp & " " & M(T - 1)
    Debug.Print T
        If T Mod 100 = 0 Then
        X = X + 1: Cel.Offset(0, X) = Trim(temp): temp = ""
        End If
    Next T
End If
Next Cel
End Sub

 

[Joe4]

Thank you, Joe4. The VBA in the thread worked well, but it's good to know those manual options as well in case I need/want to go that route at some point in the future. Thanks!

What I was telling you is that there are no manual options (Text-to-Columns options) for what you want to do - you need to use VBA like I described.
veyselemre and ksrinivasmurthy were nice enough to come up with the code for you.

Glad to see that it works for you!

​

 

[mlarson]

What I was telling you is that there are no manual options (Text-to-Columns options) for what you want to do - you need to use VBA like I described.
veyselemre and ksrinivasmurthy were nice enough to come up with the code for you.

Glad to see that it works for you!

​

My bad, I misunderstood what you were saying, so thanks for clarifying. By the way, I love the Calvin & Hobbes avatar... I loved those as a kid (still do) and my kids are really enjoying reading my old books. They recently tried the "if you want to be re-elected as Dad" on me recently.

And yes, thanks to veyselemre and ksrinivasmurthy for your help!