Earlier I have posted about inserting a fixed number of blank rows between each row in a data range (read that HERE). This post handles a different topic, i.e. how to count words (comma-separated) in rows and insert an equivalent number of blank rows in Google Sheets.

Assume you have the word “Student 1, Student 2, Student 3” in cell A2. I want 2 blank rows below. Below means in a new range as shown in the first screenshot below.

Actually, there are three words. I just want 2 blank rows down because I want to put some values against each word in the next column.

For “Student 1”, I can put the value in the same row. That’s the reason I have opted for two blank rows instead of three.

Table # 1:

But if you want you can tweak my formula to insert 3 blank rows.

Table # 2:

Want further clarification, right?

In cell A3, the content is “Student 4, Student 5”. In this case, I just want to insert 1 blank row below.

If there is only one word, then I don’t want to insert any rows.

Let’s start writing a formula to count words and insert an equivalent number of blank rows in Google Sheets. Please read and follow the step-by-step instructions given below.

Step # 1: Sheets Formula to Count Words to Insert Equivalent Blank Rows

First of all, I am taking one of my earlier formulas to count words that are separated by commas.

Step # 1 Formula:

=ArrayFormula(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),))

You can read the relevant tutorial that contains the above formula explanation here – How to Count Comma Separated Words in a Cell in Google Sheets.

The numbers against values in each row show the count of words. That means the number of blank rows to insert below each row.

As per the above formula, there will be two blank rows below the string “Student 1, Student 2, Student 3”.

There are three words and if you want three blank rows as per Table # 2, fine-tune the above formula right now. it would be as below.

=ArrayFormula(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",",""))+1,))

It will return the numbers like 3, 2, 1, 2, 1 instead of 2, 1, 0, 1, 0. You decide which formula you want to use.

I am going to use the first one. You can use either of the formulas as that won’t affect the steps below.

Step # 2: Repeat Row Numbers Based on Word Count

We have now the word count in C2:C. Now I am going to write a formula to repeat the row numbers based on the count of words in that row.

For example, the word count in cell C3 (row # 3) is 1. What I want is to repeat the row number 3, one time.

If the word count in cell C3 is 2, I want to repeat the row # 3, 2 times. We can use the REPT function for this purpose as below in cell D2.

=ArrayFormula(rept(row(A2:A)&" ",C2:C))

But what I want is the result like in F2:F. To get that we can first combine the repeated row numbers using Query, and then SPLIT and TRANSPOSE the same.

Query to Combine Values:

=ArrayFormula(query(rept(row(A2:A)&" ",C2:C),,9^9))

The result would be something like 2 2 3 5.

I have already detailed how can we use Query to combine values in Google Sheets – The Flexible Array Formula to Join Columns in Google Sheets.

The SPLIT function splits the Query combined row numbers to different columns in a row. The TRANSPOSE changes the orientation.

Step # 2 Formula:

=ArrayFormula(transpose(split(query(rept(row(A2:A)&" ",C2:C),,9^9)," ")))

See the screenshot above. Now you can delete the values in D2:D.

Step # 3: Generating a Column/Grid of Error Values Equivalent to Blank Rows Required

We need the number of blank rows equal to the sum of words. That means =sum(C2:C) which is equal to 4.

We need 4 blank rows – 2 below row # 2, 1 below row # 3, and 1 below row # 5.

We can use the SEQUENCE function to generate a column (grid in case of multiple columns in the source, for example, A2:B instead of A2:A), based on this number 4.

Enter this formula in cell G2.

=ArrayFormula(sequence(sum(C2:C),1)/0)

Note: If your range is A2:B, use 2 instead of 1 in Sequence. You can read more on the same in the last part of this post that under the title ‘Is This Formula …’

The Sequence formula returns the sequential numbers 1, 2, 3, and 4 as the row count is 4, i.e. sum(C2:C).

Parameter 2 in the Divide operator is 0. That makes the output a set of error values.

Now we must combine the Step # 2 formula above with the just above formula.

Generic Formula:

={Step # 2 Formula, Cell F2 Dormula}

Cut the cell G2 formula and then paste it with the formula in cell F2 as per the Generic formula order above.

={ArrayFormula(transpose(split(query(rept(row(A2:A)&" ",C2:C),,9^9)," "))),ArrayFormula(sequence(sum(C2:C),1)/0)}

You can remove the two ArrayFormula functions from this formula and then wrap the entire formula with one ArrayFormula.

=ArrayFormula({transpose(split(query(rept(row(A2:A)&" ",C2:C),,9^9)," ")),sequence(sum(C2:C),1)/0})

In this formula, we can replace C2:C with the formula in cell C2. Then remove the values in C2:C, I mean delete the formula in the cell C2.

Step # 3 Formula:

=ArrayFormula({transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),1)/0})

We have just completed the ‘toughest’ part of this tutorial.

Step # 4: Formula to Count Words and Insert Equivalent Blank Rows

We have now a new range with some values that include error values in F2:G. Here are the balance steps to count words and insert blank rows in Google Sheets.

First combine row numbers with the range A2:A6.

=ArrayFormula({row(A2:A6),A2:A6})

We can use infinite range A2:A instead of A2:A6 later. Right now only use a limited range.

Creating a New Range Contains Row Number Column + Source Data Column and Blank Rows

Let me explain this subtitle, which I think a little clumsy, first.

The ‘Row Number Column + Source Data Column’ in the subtitle refers to the just above formula in cell C2. ‘Blank Rows’ refers to Step # 3 formula in cell F2.

Move the range C2:D below F2:G as per the below Generic formula.

={F2 formula;C2 formula}

Here you can see the said formula in line with the above Generic formula.

={ArrayFormula({transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),1)/0});ArrayFormula({row(A2:A6),A2:A6})}

Wrap IFERROR with the formula above to remove the #DIV/0! errors and then again wrap the formula but this time with SORT.

Step # 4 Formula:

=sort(iferror({ArrayFormula({transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),1)/0});ArrayFormula({row(A2:A6),A2:A6})}))

The above formula is my answer to count words and insert equivalent numbers of blank rows in Google Sheets. But we can tweak this formula further.

I have used SORT in the above formula. So we can remove ArrayFormula functions (both SORT and ArrayFormula are not required together in a formula if the latter is inside the SORT).

=sort(iferror({{transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),1)/0};{row(A2:A6),A2:A6}}))

Further, there is an unwanted column at the beginning of the output that contains row numbers. Using Query we can remove that column. Also replace A2:A6 with A2:A.

Final Formula:

=query(sort(iferror({{transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),1)/0};{row(A2:A),A2:A}})),"Select Col2")

Is This Formula Flexible Enough to Include More Than One Column?

Yes! You should only want to make minor changes in the formula. I will explain where the changes are in the formula.

=query(sort(iferror({{transpose(split(query(rept(row(A2:A)&" ",if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),,9^9)," ")),sequence(sum(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),)),2)/0};{row(A2:A),A2:B}})),"Select Col2,Col3")

Actually, there are three changes to be made in the formula. I have marked those changes in the screenshot.

This time there are two columns in the source. We want to count words in column 1 and insert equivalent blank rows. But the output must return two columns. How?

1. If there are 2 columns, use 2 in the Sequence part of the formula, i.e the number before division operator as 2)/0. Change 2 to 3 if there are 3 columns.
2. Replace A2:A with A2:B for two columns, A2:C for three columns that depending on your range.
3. Specify the column numbers in the Query ‘Select’ part accordingly.