Split Excel Text into 42-Character Chunks Without Breaking Words
Question details
Split long product descriptions into separate sections of no more than 42 characters without splitting words.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A product description in a spreadsheet cell needs to be divided into word-safe sections for separate columns.
- Observed behavior
- Each section should contain complete words and stay within the 42-character limit.
Place a description in A1 and leave the columns to its right empty. The formulas treat ordinary spaces as word separators and normalize repeated spaces; replace line breaks and nonbreaking spaces with ordinary spaces first.
Split descriptions with a repeating pair of Excel formulas
Use a chunk formula and a remainder formula to split text without VBA.
The formulas produce chunks in alternating columns B, D, F, and so on. The columns between them hold the remaining text.
A word longer than 42 characters cannot fit without being split. If a remaining section starts with such a word, its chunk formula returns a blank; shorten that word or increase the limit.
In B1, enter =IF(LEN(TRIM(A1))<=42,TRIM(A1),IFERROR(LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(LEFT(TRIM(A1),43)," ",CHAR(1),LEN(LEFT(TRIM(A1),43))-LEN(SUBSTITUTE(LEFT(TRIM(A1),43)," ",""))))-1),""))
In C1, enter =IF(B1="","",TRIM(MID(TRIM(A1),LEN(B1)+1,LEN(TRIM(A1)))))
Select B1:C1 and drag the fill handle to the right in pairs until the remainder columns are empty. Copy the formulas down for additional descriptions in column A.
Check the remainder columns for text left after a blank chunk. To place the results in adjacent columns, copy the chunk cells from B, D, F, and subsequent alternating columns and paste them as values into a separate area.

Create word-safe description chunks in WPS Spreadsheets
Use the same chunk and remainder formulas in WPS Spreadsheets to divide product descriptions into sections of up to 42 characters.
- 1. Open the spreadsheet: Open your Excel file in WPS Spreadsheets and place a product description in A1, with empty columns to its right.
- 2. Enter the chunk formula: In B1, enter =IF(LEN(TRIM(A1))<=42,TRIM(A1),IFERROR(LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(LEFT(TRIM(A1),43)," ",CHAR(1),LEN(LEFT(TRIM(A1),43))-LEN(SUBSTITUTE(LEFT(TRIM(A1),43)," ",""))))-1),""))
- 3. Enter the remainder formula: In C1, enter =IF(B1="","",TRIM(MID(TRIM(A1),LEN(B1)+1,LEN(TRIM(A1)))))
- 4. Fill and check the results: Select B1:C1 and drag the fill handle right in pairs. Chunks appear in B, D, F, and subsequent alternating columns; check for a word longer than 42 characters if a chunk is blank while text remains.
- 5. Place chunks in adjacent columns: Copy the chunk cells and paste them as values into adjacent columns in a separate area, then save the workbook.

Frequently Asked Questions
What happens if a single word is longer than 42 characters?
It cannot fit in a 42-character section without being split. The formula returns a blank when it reaches that word; shorten the word or use a larger limit.
How do I apply the formulas to more descriptions?
Put each description in column A, select the formula cells in the first row, and drag the fill handle down. Each row will use the description in its own A cell.
Why are the chunks in alternating columns?
Each chunk formula is followed by a helper formula that calculates the remaining text. Copy the chunks from columns B, D, F, and so on, then paste them as values into adjacent columns.
Can I use a different character limit?
Yes. In the chunk formula, replace 42 with the desired limit and replace each 43 with one more than that limit. The remainder formula does not need to change.




