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

Data Analysis

  • Chart Axes in Excel
  • How to sum a total in multiple Excel tables
  • Everything about Charts in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Understanding Anova in Excel

References

  • Convert text string to valid reference in Excel using Indirect function
  • Merge tables with VLOOKUP in Excel
  • How to get relative row numbers in a range in Excel
  • Count rows with at least n matching values
  • How to get first row number in range in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only

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.

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.

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.

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

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
  • IF with wildcards in Excel
  • OR function Examples in Excel
  • Invoice status with nested if in Excel
  • How to use Excel AND Function

Date Time

  • Check If Two Dates are same month in Excel
  • Basic timesheet formula with breaks in Excel
  • Convert time to time zone in Excel
  • Pad week numbers with zeros in Excel
  • Convert text to date in Excel

Grouping

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

General

  • How to get random value from list or table in Excel
  • Count cells less than in Excel
  • Subtotal by invoice number in Excel
  • How to generate random number between two numbers in Excel
  • Print Excel Sheet In Landscape Or Portrait
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning