Using Regexreplace and Split, it is to easy to split a text string after every nth word in Google Sheets.

Assume, you have imported or copy-pasted a long text or sentence into a cell in Google Sheets.

For data processing or for some other purpose, you may want to split that long text in a cell into multiple rows.

For example, see this Lorem Ipsum place holder text in cell B4.

I have wrapped the string and due to wrapping, the row width got increased to accommodate the full text.

I don’t want to increase the width of the row, still want to read it. If so, instead of wrapping the text, what I want is to split the text after every nth word.

As you can see in the example, the text in cell B4 is split into multiple rows and each row is containing 5 words.

How to split a text in a cell after every nth word as above in Google Sheets?

As mentioned in the title, for this, we can use the REGEXREPLACE and SPLIT functions in Google Sheets. Here is how.

Logic:

1. Insert a fish delimiter after every nth word – using REGEXREPLACE.
2. Split the text at fish delimiters – using the SPLIT.
3. Finally, change the split columns to row – using TRANSPOSE.

Regexreplace Formula to Insert Delimiters at Every Nth Word

Our long text is in cell B4. So let’s write the Regexreplace in cell D4.

=REGEXREPLACE(B4,"(([a-zA-Z'‘’:.,_?!”“)(}{\]\[]+\s){5})", "$1🐟")

The above is the Regexreplace formula to insert a fish delimiter after every 5th word.

In the formula, please see the number within the curly braces. That number determines the nth.

Do you want an explanation to the above formula? Then first see the syntax of the function in use in the formula.

Syntax: REGEXREPLACE(text, regular_expression, replacement)

Actually in the formula the regular_expression is as simple as this.

"(([a-zA-Z]+\s){5})"

But in all the cases, it may not correctly place the delimiter after every nth word.

That means, if your long string contains special characters such as a period, hyphen, exclamation, brackets, etc., then the above simple regular_expression may falter.

So I have included a few of the special characters within the regular expression to solve the issue.

In addition to that instead of B4, you can use clean(B4) to clean extra spaces in the text in cell B4 (not used in my formula).

The result looks cool because of the fish delimiter, right?

Split the Text after Every Nth Word Based on the Fish Delimiter in Google Sheets

Since we have correctly placed the fish delimiters, we can effectively use the Split function.

Let’s wrap the D4 formula with the Split. This will place the split text in the same row in multiple columns.

=split(REGEXREPLACE(B4,"(([a-zA-Z'‘’:.,_?!”“)(}{\]\[]+\s){5})", "$1🐟"),"🐟")

So additionally use the Transpose function with the Split + Regex to change the data orientation from row to column.

=TRANSPOSE(split(REGEXREPLACE(B4,"(([a-zA-Z'‘’:.,_?!”“)(}{\]\[]+\s){5})", "$1🐟"),"🐟"))

The above is the final formula to split a text after every nth (here 5th) word in Google Sheets.

You can dynamically control the position to split by pointing the nth in to a cell. Instead of hardcoding you can type 5 in cell C4 and refer to that in the formula.

For that replace {5} in the formula with {"&C4&"}.

This way we can split a text after every nth word in Google Sheets.

Thanks for the stay. Enjoy!