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

Data Analysis

  • How to create Checklist in Excel
  • How To Load Analysis ToolPak in Excel
  • How to Create Area Chart in Excel
  • Use Data Form to input, edit and delete records in Excel
  • Excel Frequency Function Example

References

  • Count rows with at least n matching values
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in text data in Excel
  • How to get relative row numbers in a range in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation date in specific year
  • How To Create Drop-down List in Excel
  • Excel Data validation must not contain
  • Excel Data validation exists in list
  • Excel Data validation only dates between

How to extract last two words from text string in Excel

by

To extract the last two words from a cell, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN.

Formula

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,100)

Explanation

In the example shown, the formula in C5 is:

=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1))+1,100)

How this formula works

At the core, this formula uses the MID function to extract characters starting at the second to last space.  The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters to extract.

The text comes from column B, and the number of characters can be any large number that will ensure the last two words are extracted. The challenge is to determine the starting position, which is just after the second to last space. The clever work is done primarily with the SUBSTITUTE function, which has an optional argument called instance number.  This feature is used to replace the second to last space in the text with the “@” character, which is then located with the FIND function.

Worked Example:   Highlight cells that contain in Excel

Working from the inside out, the snippet below figures out how many spaces are in the text total, from which 1 is subtracted.

LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1

In the example shown, there are 5 spaces in the text, so the code above returns 4. This number is fed into the outer SUBSTITUTE function as instance number:

SUBSTITUTE(B5," ","@",4)

This causes SUBSTITUTE to replace the forth space character with “@”. The choice of @ is arbitrary. You can use any character that will not appear in the original text.

Worked Example:   Convert text to numbers using VALUE function in Excel

Next, the FIND locates the “@” character in the text:

FIND("@","A stitch in time@saves nine")

The result of FIND is 17, to which 1 is added to get 18. This is the starting position, and goes into the MID function as the second argument. For simplicity, the number of characters to extract is hardcoded as 100. This number is arbitrary and can be adjusted to fit the situation.

Worked Example:   Extract last name from full name -- Manipulating NAMES in Excel

Extract last N words from cell

This formula can be generalized to extract the last N words from a cell by replacing the hardcoded 1 in the example with (N-1). In addition, if you are extracting many words, you may want to replace the hardcoded argument in MID, 100, with a larger number. To guarantee you the number is large enough, you could simply use the LEN function as follows:

=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-(N-1)))+1,LEN(B5))

Post navigation

Previous Post:

How to use double quotes inside a formula in Excel

Next Post:

How to extract multiple lines from 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

Logical Functions

  • How to use Excel AND Function
  • How to use Excel NOT Function
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel OR Function

Date Time

  • Extract time from a date and time in Excel
  • Convert Unix time stamp to Excel date
  • How to show last updated date stamp in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Count day of week between dates in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Convert column number to letter in Excel
  • How to increase by percentage in Excel
  • Sum by group in Excel
  • How to calculate percent of goal in Excel
  • How to Delete Cells, Row and Rows in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning