Quick, clean, and to the point

Extract multiple lines from a cell

Excel formula: Extract multiple lines from a cell
Generic formula 
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))

To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN. 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)))

In the generic formula above, "N" represents the "nth line".


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: In older versions of Excel on a Mac, use CHAR(13) instead of CHAR(10). 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 extra space characters and returns just the line text.

Text to Columns

Don't forget that Excel has a built-in Text to Columns feature that can split text according to the delimiter of your choice, though it is not a dynamic solution like a formula. On Windows, you can type Control + J to enter the equivalent of a new line character for the "Other" delimiter. You can also use Control + J for new line during search and replace operations.

I'm not sure how to enter a new line character in Mac Excel, as a delimiter, or in the search and replace dialog. If you know how, please leave a comment below.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas