Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Load Analysis ToolPak in Excel
  • How To Create Pareto Chart in Excel
  • Understanding Pivot Tables in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How To Compare Two Lists in Excel

References

  • How to use Excel MATCH Function
  • Left Lookup in Excel
  • Lookup entire row in Excel
  • How to reference named range different sheet in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Data validation must not exist in list
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation unique values only
  • Excel Data validation don’t exceed total

How to extract multiple lines from a cell in Excel

by

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.

Post navigation

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • Invoice status with nested if in Excel
  • How to use Excel AND Function
  • Nested IF function example in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Series of dates by day
  • Get fiscal year from date in Excel
  • Calculate total hours that fall between two times in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • Get work hours between dates custom schedule in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to calculate decrease by percentage in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to choose page/paper size in Excel before Printing
  • How to fill cell ranges with random number from fixed set of options in Excel
  • Excel Operators
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning