How to Extract last two words from cell in Excel

Extracting the last two words from a cell in Microsoft Excel is a valuable skill for data cleaning, preparation, and analysis. This technique can be particularly useful when dealing with textual data, such as names, addresses, product descriptions, or any scenario where only the final part of the string is needed for analysis, reports, or data validation. Understanding how to efficiently perform this task can save time, reduce errors, and enhance the quality of your data insights.

??Purchase our book to improve your Excel productivity

Benefits

Being adept at extracting text segments from cells allows you to:

• Enhance Data Analysis: Focus on relevant parts of text data for analysis or reporting.
• Improve Data Quality: Clean and standardize textual data by removing unnecessary parts.
• Automate Repetitive Tasks: Streamline processes that involve text manipulation, reducing the need for manual intervention.

Step-by-Step Guide

Step 1: Identify Your Data

Assume you have a list of full names, and you need to extract the last two words (e.g., middle and last names) from each cell. Your data is located in column A, starting from cell A2.

Step 2: Use the RIGHT, LEN, and FIND Functions

The strategy involves using a combination of Excel functions to isolate the last two words. Here's how you can accomplish this:

1. Finding the Position of the Penultimate Space:

• To extract the last two words, you first need to find the position of the space that precedes the penultimate word. This can be a bit tricky because Excel doesn't have a built-in function to directly find the nth occurrence of a character from the right. However, you can use a combination of SUBSTITUTE, FIND, and LEN functions to achieve this.

1. Extracting the Last Two Words:

• Once you have the position of the required space, you can use the RIGHT function to extract everything to the right of this space, effectively giving you the last two words.

??Purchase our book to improve your Excel productivity

Example

Imagine you have a list of customer feedback comments in an Excel sheet, and you're interested in analyzing the adjectives customers use most frequently to describe your product or service. The feedback is in column A, and you've noticed that customers often conclude their feedback with key descriptive words. You decide to extract the last two words from each feedback comment for analysis.

Step-by-Step Guide

Step 1: Prepare Your Data

Assume your customer feedback is in column A, starting from cell A2. Here's an example of what the data might look like:

• A2: The customer service was incredibly helpful and understanding.
• A3: I love the new update, it's fast and reliable.
• And so on.

Step 2: Use Excel Formulas to Extract the Last Two Words

Because Excel doesn’t have a direct function to extract the last two words, you'll need to combine several functions: TRIM, RIGHT, LEN, and SUBSTITUTE.

1. Calculate the Number of Words in the String:In B2, enter the formula to calculate the number of words:=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2, " ", ""))+1This formula counts the spaces to determine the number of words, adjusting for multiple spaces between words with TRIM.
2. Extract the Last Two Words:In C2, enter the formula to extract the last two words:=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", LEN(A2))), LEN(A2)*2))This formula replaces the last space in the text with a large number of spaces (using LEN(A2) to ensure it’s sufficiently large), then uses RIGHT to extract text starting from this artificially distant last space, effectively capturing the last two words.

Step 3: Drag the Formulas Down

• Drag the formulas in B2 and C2 down their respective columns to apply them to your entire dataset.

Explanation

Let's break down the formula in C2 for clarity:

• TRIM(A2) ensures there are no leading, trailing, or multiple spaces between words.
• SUBSTITUTE(TRIM(A2), " ", REPT(" ", LEN(A2))) replaces the last space in the string with a large number of spaces. It uses REPT(" ", LEN(A2)) to ensure the replacement string is longer than the maximum possible word, forcing the last two words to the end of the string.
• RIGHT(..., LEN(A2)*2) then extracts a chunk of text from the right, which is guaranteed to include the last two words because of the large space inserted.
• Wrapping everything in TRIM(...) removes the leading spaces from the chunk, leaving just the last two words.

Advanced Tips

• Handling Different Text Structures: Before applying this technique broadly, ensure your text has a uniform structure. If variations exist, consider using conditional formulas (IF, ISERROR) to handle errors or special cases.
• Dynamic Array Formulas: If you're using Excel 365 or Excel 2019, leverage dynamic array formulas to apply the extraction to an entire column with a single formula. Adjust the formula in D2 to automatically spill down the results for all rows.
• Use of Helper Columns: While the example above uses multiple steps and helper columns for clarity, you can combine these into a single formula if needed. However, using helper columns can make troubleshooting easier, especially for complex data sets.
• Regular Expressions (RegEx) in Excel: For users comfortable with VBA or using Excel add-ins that support RegEx, you can achieve this task more directly with a regular expression pattern designed to match the last two words.

??Purchase our book to improve your Excel productivity :

??102 Most Useful Excel Functions with Examples: The Ultimate Guide

???? Order it here : https://lnkd.in/enmdA8hq

?? Transform from novice to pro with:

?? Step-by-Step Guides

??? Clear Screenshots

?? Real-World Examples

?? Downloadable Practice Workbooks

?? Advanced Tips

??Newsletters that might interest you :

??Leadership - Daily inspiration

??Motivation - Daily Inspiration

??Challenge Yourself Everyday

??Chase Happiness: Daily Triumph

??Simplify to Illuminate Mind

??Daily Habits for Health

??Peaceful Paths Mindful Morning

??Passion Path Daily Insights

??Love Notes Daily Digest

??Zen Pulse: Mindful Living