How to Extract the Last Word From a Cell in Excel
Last Updated :
20 Jun, 2024
Explore how to extract the last word from a cell in Excel. Whether you’re working with text data, names, or any other content, knowing this technique can be incredibly useful. In this article, we explain how to extract the last word from a text in a cell using the Excel function. Extracting words from a text is an important task in text processing.
Eg. Suppose we have a data file with a field called “Product_Category”, which combines both product name and their respective category name with space as below. Assume that the last word from the “Product_Category” field is a category. We have to extract categories from the given data in column B for further analysis.
Sample Data: We use the below list of 4 Excel user-defined functions to extract the last word.
The REPT() Function
Syntax:
REPT(text, number)
Where,
- text – character to repeat
- number – number of times to repeat the character
Eg: REPT(“*“,10) – returns **********
The SUBSTITUTE() Function
Syntax:
SUBSTITUTE( text, old_text, new_text, [instance_number] )
Where,
- text – the original text
- old_text – text need to replace
- new_text –text replace with old text
- [instance_number] – Optional. The number indicates the instance number of old text to replace
Eg: SUBSTITUTE(“Filo Mix”," ",REPT("*",10)) – returns Filo**********Mix
The RIGHT() Function
Syntax:
RIGHT( text, [number_of_characters] )
Where,
- text – original text
- [number_of_characters] – optional. Number characters extract from the right.
Eg: RIGHT(“Filo**********Mix”) – returns *******Mix
The TRIM() Function
Syntax:
TRIM(text)
Where,
- text – Removes leading and trailing spaces
Eg: Eg: Trim(“Mix”) – returns Mix
Follow the below steps to Extract the last word from a Cell in Excel:
Write the below formula to cells “B2”. In the given data category name is not more than 10 characters. So we used 10 in both REPT() and SUBSTITUTE(). You can use any number greater than the maximum of the last word.
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))
Conclusion
Extracting the last word from a cell in Excel can be a handy skill for data manipulation and analysis. By following the steps outlined in this guide, you’ll be able to efficiently retrieve that crucial last word from your cell contents. Remember to use dynamic formulas and functions like RIGHT, LEN, and SEARCH to achieve accurate results.
What other text-related tasks can Excel handle?
Excel can handle various text-related tasks, such as:
- Concatenating strings (using CONCATENATE or &)
- Splitting text (using LEFT, RIGHT, or MID)
- Removing duplicates (using REMOVE DUPLICATES)
- Changing case (using UPPER, LOWER, or PROPER)
How do I adapt this method for non-English words or phrases?
To adapt to non-English words or phrases, consider using functions like SEARCH or FIND with language-specific rules. Adjust the formula based on the language’s word boundaries and characters.
Similar Reads
How to Extract the last N Words from Text String in Excel Excel is a tool for storing and managing a large amount of data. Data is entered in a rectangular block, which we called a cell. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. It is primarily used for financial data analysis
3 min read
How to Extract Text Only from Alphanumeric String in Excel? In the vast landscape of Excel spreadsheets, managing data efficiently is crucial for streamlined analysis and reporting. Often, one encounters the challenge of dealing with cells containing a mix of letters and numbers, commonly known as alphanumeric strings. Extracting only the text component from
4 min read
How to Extract Text before @ Sign from an Email Address in Excel? You got a dataset and you want to extract the name part of the email address. This task can be done with a text to column and formula that uses the LEFT and FIND functions. Using LEFT and FIND functionsThe LEFT function: The LEFT function returns a given text from the left of our text string based o
3 min read
How to Extract Characters from a String in R Strings are one of R's most commonly used data types, and manipulating them is essential in many data analysis and cleaning tasks. Extracting specific characters or substrings from a string is a crucial operation. In this article, we’ll explore different methods to extract characters from a string i
4 min read
How to Find the Last Used Row and Column in Excel VBA? We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Sample Data Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCel
2 min read
How to Remove the Last Character From a Table in SQL? SQL (Structured Query Language) allows for efficient data manipulation and retrieval. A common task in SQL involves removing the last character from a specific column within a table. This can be achieved using string functions like SUBSTRING() and LEN(). In this article, we will demonstrate how to a
4 min read
How to Use the SUBTRACT Function in Excel Subtraction in Microsoft Excel - Quick StepsSelect the cell where you want the result.Type = (equal sign)> then select the first cell to subtract from.Type - (minus sign) > Select the cell you want to subtract.Press Enter.Subtraction in Excel is a basic yet powerful skill that can make your wo
10 min read
How to Separate First and Last Name in Excel: 3 Easy Methods How to Split Names in Excel - Quick StepsOpen MS Excel>>Select the ColumnOpen Text to Columns WizardChoose Delimited>>Select DelimitersSet Destination>>Adjust ColumnsManaging datasets with full names in a single column can be challenging when you need the names split into first and
10 min read
How to Separate Text and Numbers from One Cell Into Two Columns? Excel is a powerful tool for organizing, storing, and visualizing large volumes of data. Data is entered in a rectangular block(intersection of rows and columns) which we called a cell. This tool can be used to perform different tasks like creating graphs, and analyzing trends, to get insights from
3 min read
How to Separate Numbers and Percentages from One Cell in Excel? While dealing with numbered data, we generally come across a variety of problems that hamper our work. One such problem of separating numbers and percentages from one cell. In this article, we will learn how to separate numbers and percentages from one cell. Data Set The data on which to be performi
4 min read