How to extract multiple lines from a cell in Excel
To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: SUBSTITUTE, REPT, TRIM, MID, and LEN.
Formula
=TRIM(MID(SUBSTITUTE(A1,delim,REPT
(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))
Explanation
In the example shown, the formula in D5 is:
=TRIM(MID(SUBSTITUTE($C5,CHAR(10),REPT
(" ",LEN($C5))), (D$4-1)*LEN($C5)+1, LEN($C5)))
How this formula works
At the core, this formula looks for a line delimiter (“delim”) and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: For the new line delimiter on Windows, you’ll want to use CHAR(10). On Excel for Mac, use CHAR(13). The CHAR function returns a character based on it’s numeric code.
The number of spaces used to replace the line delimiter is based on the total length the text in the cell. The formula then uses the MID function to extract the desired line. The starting point is worked out with:
(N-1)*LEN(A1)+1 // start_num
Where “N” stands for “nth line”, which is picked up from row 4 with the D$4 reference.
The total characters extracted is equal to the length of the full text string:
LEN(A1) // num_chars
At this point, we have the “nth line”, surrounded by spaces.
Finally, the TRIM function slices off all preceding extra space characters and returns just the line text.