Split Excel Text into Columns with VBA Without Breaking Words
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.

- 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.
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.
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.
In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
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
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.
Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code in the workbook.

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.

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.




