Have you ever needed to count the words in a Google Sheets document and insert a specific number of blank rows corresponding to that count? If so, you’re in luck! In this article, we’ll show you how to easily count words separated by commas and insert an equivalent number of blank rows in Google Sheets.
Step 1: Counting Words with a Formula
Let’s say that you have a word list in cell A2, such as “Student 1, Student 2, Student 3”, and you want to insert two blank rows below it. To count the number of words in the cell, we can use the following formula:
=ArrayFormula(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",","")),))
This formula will return the count of words in each row. In our example, it will return the value 3. However, since we only want to insert two blank rows, we can modify the formula to =ArrayFormula(if(len(A2:A),len(A2:A)-len(SUBSTITUTE(A2:A,",",""))+1,)).
Step 2: Repeating Row Numbers Based on Word Count
Now that we have the word count in column C, we can repeat the row numbers based on the count of words in each row. For example, if the word count in cell C3 is 1, we want to repeat the row number 3 one time. We can use the REPT function for this purpose with the following formula in cell D2:
=ArrayFormula(rept(row(A2:A)&" ",C2:C))
To get the desired result, we can combine the repeated row numbers using the QUERY function, and then split and transpose the result. The formula would be:
=ArrayFormula(transpose(split(query(rept(row(A2:A)&" ",C2:C),,9^9)," ")))
Now you can delete the values in column D.
Step 3: Generating Blank Rows
To generate the required number of blank rows, we can use the SEQUENCE function. Let’s say we need four blank rows. You can enter the following formula in cell G2:
=ArrayFormula(sequence(sum(C2:C),1)/0)
Note: If you have multiple columns, you can use A2:B instead of A2:A in the SEQUENCE function.
Step 4: Combining All Steps
Now that we have all the necessary formulas, we can combine them to count words and insert the equivalent number of blank rows in Google Sheets. Copy the formula in cell G2 and paste it with the formula in cell F2 using the following order:
=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})
Finally, wrap the formula with the IFERROR and SORT functions to remove any errors and sort the results:
=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:A),A2:A})}))
Congratulations! You now have a formula that counts words and inserts the corresponding number of blank rows in Google Sheets. This formula can also be adjusted to accommodate multiple columns by making minor changes.
Remember, Google Sheets is a powerful tool that can make your life easier when dealing with data manipulation. Stay tuned for more tips and tricks on our website, Crawlan.com.
