Split Text into Groups of N Words in Google Sheets

With REGEXREPLACE and SPLIT, it’s quite easy to split text into groups of N words in Google Sheets. You might find this handy in several situations, like:

Formatting long sentences for better readability
Avoiding row height issues caused by wrapping
Preparing content for SMS or email campaigns
Displaying clean chunks of text in dashboards
And so on.

Table of Contents

Problem with Long Text in Google Sheets

Let’s say you have some long placeholder text in cell B4 — maybe a Lorem Ipsum block.

When wrapping is enabled, the row height increases to fit the full content. But what if you don’t want to expand the row and still want the text to remain readable?

Wrapped long text in a single Google Sheets cell showing increased row height

One option is to split the text after every Nth word, placing the chunks one below the other. That’s exactly what we’re going to do.

Example: Split a Text After Every Nth Word

In the example below, the text in cell B4 is split into multiple rows. Each row contains 5 words. It looks cleaner, and the row height stays controlled.

Split text into groups of N words in Google Sheets displayed in separate rows

How to Split Text into Groups of N Words in Google Sheets

We’ll use a combination of REGEXREPLACE and SPLIT, and optionally TRANSPOSE if you want the output vertically instead of horizontally.

The Logic

Use REGEXREPLACE to insert a custom delimiter after every Nth word
Use SPLIT to break the text at that delimiter
Use TRANSPOSE to flip the output from horizontal to vertical (optional)

Step 1: Add a Delimiter After Every Nth Word

Let’s assume your long text is in cell B4. Use this formula in cell D4:

=REGEXREPLACE(B4, "((\S+\s){5})", "$1🐟")

How the Formula Works:

REGEXREPLACE replaces parts of a string that match a pattern.
\S+ matches a sequence of non-whitespace characters — essentially, a “word.”
\s matches the space after each word.
(\S+\s){5} matches five words with their spaces.
$1🐟 inserts a fish emoji delimiter after every fifth word.
The outer parentheses wrap the full five-word group so $1 captures it.
You can change {5} to any other number to control how often the delimiter appears — for example, {3} to split after every 3 words.

Step 2: Split Using the Delimiter

Now that the delimiters are in place, split the text into separate parts:

=SPLIT(REGEXREPLACE(B4, "((\S+\s){5})", "$1🐟"), "🐟")

This spreads the split chunks across columns, with each chunk containing 5 words.

Step 3: Transpose the Output into Rows

If you want the result in rows instead of columns, wrap the formula with TRANSPOSE:

=TRANSPOSE(SPLIT(REGEXREPLACE(B4, "((\S+\s){5})", "$1🐟"), "🐟"))

This gives you a clean vertical list — each line holding 5 words from the original string.

Make It Dynamic (Optional)

Want to control the number of words dynamically? Type a number (say 5) in cell C4 and update the formula like this:

=TRANSPOSE(SPLIT(REGEXREPLACE(B4, "((\S+\s){" & C4 & "})", "$1🐟"), "🐟"))

Now you can change the value in C4 to split after 3, 10, or any number of words — no formula editing needed.

Summary

If you’re working with long strings in a single cell, this method gives you a flexible way to split text into groups of N words in Google Sheets. Whether you’re prepping content for SMS, building dashboard displays, or just making long text easier to read — this formula combo gets the job done.

Resources

If you’re working with text and splits, here are more tutorials that might help: