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.
 we can extract the required/every nth line from multi-line cells in Google Sheets.){kind=link}
Hi,
I’m using the formula:
=RIGHT(REGEXEXTRACT(A1,"(\n.*){14}"), LEN(REGEXEXTRACT(A1,"(\n.*){14}"))-1)to extract this line from A1: Item Level: 78
Is there any way I can get rid of the “Item Level: ” and just have the number show up?
Thanks,
Eryk
Hi, Eryk Davidson,
You can try this Regexextract formula.
=regexextract(REGEXEXTRACT(A1,"(\n.*){14}"),"([\d\.]+)")Hi, Spencer Barfuss,
Your following formula is correct since you are using the correct Timeofday literal, but you need two fixes.
Your Current Formula:
=query('Form Responses 1'!A2:F,"select * where F < timeofday '"&text(G2,"HH:MM:SS")&"' ORDER BY F",0)1. Your criteria in cell G2 is a timestamp. It should be a time.
2. Your source data column F extracts time from column A in that sheet but does not return proper time values.
I have fixed point # 1 above in my sample sheet, i.e., "Copy of Early", in your file.
Regarding point # 2, I have used a virtual column F with proper time values.
Here you go!
=ArrayFormula(query({'Form Responses 1'!A2:E,mod('Form Responses 1'!A2:A,1)},"select Col1 where Col6 < timeofday '"&text(G2,"HH:MM:SS")&"'",0))
Thanks – this is great, except that it returns a line of text that begins with a newline.
I stripped the newline by using;
=RIGHT(REGEXEXTRACT(A2,"(\n.*){2}"), LEN(REGEXEXTRACT(A2,"(\n.*){2}"))-1)Is there a cleaner way to do that?
Hi, J Mann,
Simply wrap with TRIM.
=trim(REGEXEXTRACT(A2,"(\n.*){2}"))Each cell can have a different number of lines. but we know:
1st line is always the NAME.
The penultimate line is always the POSTCODE and CITY.
The last line is always the COUNTRY.
Of course, I can figure out how many lines are there in each cell by counting the next line character.
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))If the formula returns 4, then there are five lines.
I mean, sure, I can get country for this cell with
=REGEXEXTRACT(A2,"(\n.*){4}")But the
{4}above is manual input, which defeats the purpose.Ideas?
Hi, Gumanjee,
You can replace –
=REGEXEXTRACT(A2,"(\n.*){4}")– with
=REGEXEXTRACT(A2,"(\n.*)"&"{"&LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))&"}")I hope the above makes sense.
Is there a way to choose the LAST line, regardless of how many lines are in the cell?
Hi, yehonatan Elazar,
You can try this.
=index(split(A2,char(10)),0,COUNTA(split(A2,char(10))))The formula extracts the last line as follows.
1. Splits values into individual columns based on the new line character.
split(A2,char(10))2. Counts the split values (words).
COUNTA(split(A2,char(10)))3. Using Index, offset the words based on count.