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

Lookup and Reference Examples

  • Excel Advanced Lookup using Index and Match Functions
  • Count unique text values with criteria
  • How to get last row in numeric data in Excel
  • Perform case-sensitive Lookup in Excel
  • How to use Excel MMULT Function

Data Analysis Examples

  • How to create a Histogram in Excel
  • How to sum a total in multiple Excel tables
  • How to perform a t-Test in Excel
  • Get column index in Excel Table
  • How to create dynamic reference table name in Excel

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Data validation must not exist in list
  • Excel Data validation exists in list

How to get last line in cell in Excel

by

To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions.

Formula

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell. If you have longer lines, increase this number as needed.

Worked Example:   Join cells with comma in Excel

Explanation

In the example shown, the formula in C5 is:

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

How this formula works

This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and “flood” the text with spaces where we find one. Then we come back and grab text from the right.

Worked Example:   How to extract last two words from text string in Excel

Working from the inside out, we use the SUBSTITUTE function to find all line breaks (char 10)  in the text, and replace each one with 200 spaces:

SUBSTITUTE(B5,CHAR(10),REPT(" ",200))

After the substitution, the looks like this (with hyphens marking spaces for readability):

line one----------line two----------line three

With 200 spaces between each line of text.

Worked Example:   Split numbers from units of measure in Excel

Next, the RIGHT function extracts 200 characters, starting from the right. The result will look like this:

-------line three

Finally, the TRIM function removes all leading spaces, and returns the last line.

 

Post navigation

Previous Post:

How to count specific characters in a range in Excel

Next Post:

How to count specific words in a cell 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
  • CONCATENATE function: Description, Usage, Syntax, Examples and Explanation
  • How to extract domain name from URL in Excel
  • SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
  • How to extract word containing specific text in Excel
  • How to count keywords in a range of cell
  • Add workdays no weekends in Excel
  • Extract date from a date and time in Excel
  • Get fiscal quarter from date in Excel
  • Generate series of dates by weekends in Excel
  • Convert date to text in Excel
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • DOLLARFR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles