It’s not common to use, at least in my case, multiple lines in cells. But I do use/insert multiple lines sometimes. I hope this is the case with you also. In such a case, it would be nice to know how to extract every nth line from multi-line cells in Google Sheets.

Are you new to the term multiple lines in cells in Google Sheets?

Multiple lines in a cell mean contents in multiple lines in a cell or you can say multiple rows within a cell.

Yes! You can split a cell into multiple lines and enter text in each line. Understanding this is a must to proceed further. Here is an example of using multiple lines in cells in Google Sheets.

To know how to get such multiple lines, please read my relevant guide here – Start New Lines Within a Cell in Google Sheets – Desktop and Mobile.

As per the above (screenshot) example, there are values in multiple lines in cell C2, C3, C4, and C5. Assume you want to filter the second line from each and every multi-line cells above.

Unlike data entered in rows, it’s not easy for a novice Google Sheets user to extract content from multi-line cells. You can’t filter content in multiple-line cells. Instead, you can extract the lines.

Using REGEXEXTRACT (RE2 regular expression based extracting) we can extract the required/every nth line from multi-line cells in Google Sheets.

The formula to extract every second, third, fourth line… from each cell and the formula to extract every first line from each cell is different. That means there will be two different formulas to extract/filter every nth line from a cell in Google Sheets.

Regexextract to Extract/Filter Every First Line from Multi-Line Cells

To extract the first line from a multiple line cell, use the below REGEXEXTRACT formula.

=REGEXEXTRACT(A2,"(\w.*)")

The above formula will extract the name “Jessica Coleman” which is in the first line in cell A2.

When there are several cells with newlines in a column (please refer to the screenshot below), we can modify the above formula as an array formula.

=ArrayFormula(if(len(A2:A),REGEXEXTRACT(A2:A,"(\w.*)"),))

The newline is actually the output of =CHAR(10). If you key this formula in any blank cell in your Sheet, it will create a new line in that cell.

I am not attempting to explain this formula here as it’s included in my above-linked guide. My point here is how to find the new line character using REGEX in Google Sheets. It is the key to extracting/filtering every nth line from a multi-line cell.

For your info, in the above formula that extracts the first line from a cell, the matching of the newline character is not required.

How to Match New Line Character in REGEX Formulas in Google Sheets

If you refer to GitHub’s RE2 regular expression syntax reference, you can find the regular expression to match newline character and it is \n.

In Sheets’ REGEXEXTRACT, we can use the following regular expression to match the newline and extract the following text.

(\n.*)

Now let’s go to the formula to extract every nth line from multi-line cells in Google Sheets.

Regexextract to Extract/Filter Nth Line (Except First Line) from Multiple Line Cells

To extract the second row in a cell, cell A2 here, use this REGEXEXTRACT formula.

=REGEXEXTRACT(A2,"(\n.*){1}")

It will extract the second row/line content in cell A2, which is “Tina Walker”.

To extract the third row from a multiple line cell, what you want to do is to modify the quantifier {1} to {2}.

The quantifier 1 matches the newline character one time, 2 matches two times and so on.

=REGEXEXTRACT(A2,"(\n.*){2}")

This formula extracts the third name from cell A2, which is “Sara Richardson” in row/line 3 (after two newline characters). Can we convert the above as an array formula?

Yes, we can! Here is the array formula to extract every nth line (here 3rd line) from multi-line cells in Google Sheets.

=ArrayFormula(if(len(A2:A),REGEXEXTRACT(A2:A,"(\n.*){2}"),))

Solving Formula Error If There Is No Nth Line in a Cell

If there is no nth line to match in any one of the cells in the range, the Regexextract array formula would return #N/A error in such cells. To return blank, use the formula as follows.

=ArrayFormula(IFNA(if(len(A2:A),REGEXEXTRACT(A2:A,"(\n.*){2}"),)))

That’s all. Enjoy!

Related Reading: How to Move New Lines in a Cell to Columns in Google Sheets.