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
  • Vlookup Examples in Excel
  • How to use Excel COLUMN Function
  • How to get address of named range in Excel
  • How to use Excel FORMULATEXT function

Data Analysis Examples

  • Remove Duplicates Example in Excel
  • Reverse List in Excel
  • How to calculate current stock or inventory in Excel
  • Get column index in Excel Table
  • Conditional Formatting Data bars Examples in Excel

Data Validation Examples

  • Excel Data validation with conditional list
  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation must begin with

How to get last word in a 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(text," ",REPT(" ",100)),100))

Explanation

In the example shown, the formula in C6 is:

=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(" ",100)),100))

Which returns the word “time”.

How this formula works

This formula is an interesting example of a “brute force” approach that takes advantage of the fact that TRIM will remove any number of leading spaces.

Worked Example:   How to find nth occurrence of character in Excel

Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100 spaces:

SUBSTITUTE(B6," ",REPT(" ",100))

So, for example, with the text string “one two three” the result is going to look like this:

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

With hyphens representing spaces for readability. Keep in mind that there will be 100 spaces between each word.

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

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

-------three

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

Note: We are using 100 arbitrarily because that should be a big enough number to handle very long words. If you have some odd situation with super long words, bump this number up as needed.

Worked Example:   How to extract word containing specific text in Excel

Handling inconsistent spacing

If the text you are working with has inconsistent spacing (i.e. extra spaces between words, extra leading or trailing spaces, etc.) This formula won’t work correctly. To handle this situation, add an extra TRIM function inside the substitute function like so:

=TRIM(RIGHT(SUBSTITUTE(TRIM(B6)," ",REPT(" ",100)),100))

This will normalize all spaces before the main logic runs.

Post navigation

Previous Post:

How to find and replace multiple values at same time in Excel

Next Post:

How to get first word 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
  • How to calculate Number of Instances in Excel
  • How to extract domain from email address in Excel
  • How to pad text to match equal length in Excel
  • T function: Description, Usage, Syntax, Examples and Explanation
  • How to count total words in a range in Excel
  • Count holidays between two dates in Excel
  • Convert Excel time to decimal seconds
  • Convert time to time zone in Excel
  • How to calculate next scheduled event in Excel
  • Calculate series of dates by workdays in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles