Have you ever encountered cells with multiple lines while working in Google Sheets? It may not happen often, but there are times when you need to work with or extract specific content from cells that have content spread across different rows. Understanding how to handle multiple lines within a cell is crucial for maximizing the potential of Google Sheets.
In this article, I’m going to share with you a technique to extract every nth line from multi-line cells in Google Sheets. Whether you’re a beginner or an experienced user, this method will prove useful when you need to filter or extract specific content from cells with multiple lines.
Extracting the First Line from Multi-Line Cells
Let’s start by focusing on extracting the first line from a cell that contains multiple lines. You can achieve this using the REGEXEXTRACT formula. Here’s how it works:
=REGEXEXTRACT(A2,"(w.*)")In the example above, the formula will extract the name “Jessica Coleman,” which is located on the first line in cell A2. However, if you have multiple cells with newlines in a column, the formula needs to be modified to work as an array formula:
=ArrayFormula(if(len(A2:A),REGEXEXTRACT(A2:A,"(w.*)"),))
It’s worth noting that the newline character is represented by CHAR(10). By using this formula in a blank cell, you can create a new line within that cell. If you’re interested in learning more about this formula, I recommend checking out my detailed guide on how to start new lines within a cell in Google Sheets.
Now that you know how to extract the first line from a multi-line cell, you might be wondering how to match the newline character in REGEX formulas in Google Sheets. According to GitHub’s RE2 regular expression syntax reference, you can match the newline character using n. In Sheets’ REGEXEXTRACT, you can use the following regular expression to match the newline and extract the subsequent text:
(n.*)Let’s dive into how to extract every nth line from multi-line cells in Google Sheets.
Extracting the Nth Line (Except the First Line) from Multi-Line Cells
To extract the second line from a cell, such as cell A2, you can utilize the following REGEXEXTRACT formula:
=REGEXEXTRACT(A2,"(n.*){1}")This formula will extract the content of the second row in cell A2, which, in this case, is “Tina Walker.” To extract the third row from a multi-line cell, you’ll need to modify the quantifier {1} to {2}:
=REGEXEXTRACT(A2,"(n.*){2}")The above formula extracts the third name, “Sara Richardson,” from cell A2 in the third row (after two newline characters). Moreover, you can convert this formula into an array formula to extract every nth line (in this example, the third line) from multi-line cells:
=ArrayFormula(if(len(A2:A),REGEXEXTRACT(A2:A,"(n.*){2}"),))But what if there is no nth line to match in any of the cells within the range? In such cases, the REGEXEXTRACT array formula would return an #N/A error. To return a blank cell instead, you can modify the formula as follows:
=ArrayFormula(IFNA(if(len(A2:A),REGEXEXTRACT(A2:A,"(n.*){2}"),)))And there you have it! With these techniques, you can effortlessly extract every nth line from multi-line cells in Google Sheets.
Don’t forget to check out my related article on how to move new lines in a cell to columns in Google Sheets for further reading.
Now go ahead and make the most of Google Sheets’ capabilities. Enjoy!
To learn more about Google Sheets and its features, visit Crawlan.com.
