Split text into length restricted columns by whole words

teronyka · Dec 13th 2019

Hi,

I need to split a long text entry to 2 columns which are limited to 30 characters each + any remaining text will be placed in 3rd column.

The text needs to be split by whole words (so the split will not be done mid-word)

See attached for reference

- Column A shows the original entry

- Column B needs to have first max 30 characters, but split by whole words

- Column C needs to have next max 30 characters, but split by whole words

- Columnd D will have all remaining characters

Any smart ways to get it automated?

forum.ozgrid.com/index.php?attachment/1226348/

**pike** · Dec 14th 2019

Hi teronyka,

Welcome to the forum.

You can split a text string by length and at certain characters like a Space by using a combination of the LEFT, RIGHT, LEN, and FIND functions.

**KjBox** · Dec 14th 2019

Your attachment shows "Page not found" error when I try to download it.

Does your data start in A2 and A1 is a header?

**KjBox** · Dec 14th 2019

Try the attached sample file (it uses Row 1 as header row with data starting in A2). Click the button

Code assigned to the button is

Code

Sub SplitText()
    Dim x, y, z, i As Long, ii As Long
    
    With ActiveSheet
        x = .Cells(1).CurrentRegion
        ReDim y(1 To UBound(x), 1 To 3)
        For i = 2 To UBound(x)
            z = Split(x(i, 1))
            For ii = LBound(z) To UBound(z)
                If Len(y(i, 1)) + Len(z(ii)) < 30 And y(i, 2) = "" Then
                    y(i, 1) = Trim(y(i, 1) & " " & z(ii))
                ElseIf Len(y(i, 2)) + Len(z(ii)) < 30 And y(i, 3) = "" Then
                    y(i, 2) = Trim(y(i, 2) & " " & z(ii))
                Else
                    y(i, 3) = Trim(y(i, 3) & " " & z(ii))
                End If
            Next
        Next
        .[b1].Resize(UBound(y), 3) = y
        .Columns(2).Resize(, 3).AutoFit
    End With
    
End Sub

Display More

Split text into length restricted columns by whole words

Participate now!

Share

Tags