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

Data Analysis

  • How To Filter Data in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Remove Duplicates Example in Excel
  • Managing Conditional Formatting Rules in Excel
  • Working With Tables in Excel

References

  • Vlookup Examples in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • Offset in Excel
  • How to use Excel MMULT Function
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Data validation must not exist in list

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:   Extract word that begins with specific character 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:   How to remove trailing slash from url 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 specific characters 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:

Put names into proper case — Manipulating NAMES in Excel

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

  • Nested IF function example in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert time to time zone in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get month name from date in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into unequal buckets in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to generate random number weighted probability in Excel
  • Lock Cells in a Worksheet Excel
  • How to get original price from percentage discount in Excel
  • How to get random value from list or table in Excel
  • How to calculate percent of students absent in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning