VBA & Macro Problems

Split Excel Text into Columns with VBA Without Breaking Words

Natalie TaylorNatalie Taylor Nov 14, 2026 869 views

Question details

Split text from each cell in column A into no more than 20 cells, with a maximum of 30 characters per cell and word breaks avoided where possible.

Split Excel Text into Columns with VBA Without Breaking Words
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A user needs a VBA macro to divide text across columns while respecting character and column limits.
Observed behavior
Each output cell should contain at most 30 characters, with splits made at spaces where possible and any text exceeding 20 output cells flagged.
Before you start

Place the source text in column A, with a header in row 1. Make sure columns B through V are available: the macro clears them for output and overflow status.

Solution 1Recommended

Split column A into 30-character cells with an Excel VBA macro

Use this macro when text needs to be split at spaces into a maximum of 20 output columns.

The macro writes results to columns B through U and uses column V to flag text that does not fit. It trims spaces at the edges of each segment, while leaving the original text in column A unchanged.

A word longer than 30 characters must be split to meet the cell limit. The macro uses ordinary spaces as word boundaries.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Insert the macro

In the VBA editor, select Insert > Module and paste this code: Sub SplitTextIntoColumns() Dim ws As Worksheet Dim lastRow As Long, r As Long, col As Long Dim textLeft As String, part As String Dim splitAt As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then Exit Sub ws.Range("B2:V" & lastRow).ClearContents For r = 2 To lastRow If IsError(ws.Cells(r, "A").Value) Then ws.Cells(r, "V").Value = "Source cell contains an error" Else textLeft = Trim$(CStr(ws.Cells(r, "A").Value)) col = 2 Do While Len(textLeft) > 0 And col <= 21 If Len(textLeft) <= 30 Then part = textLeft textLeft = "" Else splitAt = InStrRev(Left$(textLeft, 30), " ") If splitAt > 0 Then part = Trim$(Left$(textLeft, splitAt - 1)) textLeft = Trim$(Mid$(textLeft, splitAt + 1)) Else part = Left$(textLeft, 30) textLeft = Trim$(Mid$(textLeft, 31)) End If End If ws.Cells(r, col).Value = part col = col + 1 Loop If Len(textLeft) > 0 Then ws.Cells(r, "V").Value = "Overflow: text exceeds 20 columns" End If End If Next r End Sub

3
Run and check the results

Return to the worksheet, select Developer > Macros, choose SplitTextIntoColumns, and select Run. Check columns B through U for the segments and column V for any overflow or source-cell errors.

4
Save the macro if needed

Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code in the workbook.

Split column A into 30-character cells with an Excel VBA macro
Overflow remains in the source: If column V reports overflow, not all text was written to the output columns. The complete original remains in column A; review it before using the split results.
Free Microsoft Office alternative

Work with spreadsheet text in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and move between Office applications with ease; the Excel VBA macro above is not a guaranteed WPS macro solution.

Work with spreadsheet text in a familiar interfaceOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a free, lightweight Office alternativeMove existing spreadsheets into a familiar workspace
microsoft office alternative - wps office

Frequently Asked Questions

What happens if one word is longer than 30 characters?

The macro splits that word after 30 characters because keeping it intact would exceed the cell limit.

How can I tell if the text needs more than 20 columns?

Check column V after running the macro. It flags overflow when text remains after columns B through U are filled. The full source text stays in column A.

Will Excel Text to Columns enforce the 30-character limit?

Data > Text to Columns can split text using a consistent delimiter, but a delimiter-based split alone does not ensure each result is 30 characters or less. Use the VBA macro for both the length and column limits.

Why does the macro start at row 2?

The code treats row 1 as a header. Put source text below the header in column A before running it from Developer > Macros.