Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel MMULT Function
  • How to use Excel MATCH Function
  • Extract data with helper column in Excel
  • How to use Excel OFFSET function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to Create Column Chart in Excel
  • How To Create Frequency Distribution in Excel
  • How to count table rows in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How to Create Area Chart in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation with conditional list
  • Excel Data validation must contain specific text
  • Excel Data validation allow uppercase only
  • Excel Data validation must not contain

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.

Worked Example:   How to translate letters to numbers in Excel

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.

Worked Example:   Extract word that begins with specific character in Excel

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.

Worked Example:   How to count total words in a cell in Excel

Finally, the TRIM function slices off all preceding extra space characters and returns just the line text.

Post navigation

Previous Post:

How to extract last two words from text string in Excel

Next Post:

How to extract nth word from text string in excel

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
  • Split text and numbers in Excel
  • How to extract name from email address in Excel
  • How to display conditional message with REPT function in Excel
  • How to translate letters to numbers in Excel
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to get Weekdays, Working days between Two Dates in Excel
  • Get day name from date in Excel
  • How to determine year is a leap year in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate nth day of week in month in Excel
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles