How to Extract Last Word in Excel [8 Best Ways]

ByJames Corrigan

While working with complex datasets in Microsoft Excel, you may often need to extract data to the right of a certain character or word. This can be especially challenging if you are dealing with a large volume of data that is structured in a single column or string of text.

Before you start learning different techniques to extract the last word in excel, let’s take a moment to understand the delimiter.

A delimiter is a character or collection of characters that is used to indicate the start or end of a word, string, or data field. Spaces, tabs, commas, and semicolons are all common delimiters.

Excel is a powerful tool for data analysis. There are a few different ways to extract word in excel. In this blog post, you will learn 9 different methods to extract word from a text in Excel.

Although there is no formula in Excel to directly extract the last word. You can use combinations of the excel basic functions TRIM, RIGHT, SUBSTITUTE, and REPT to extract the last word, or use advanced excel functions TEXTSPLIT and TEXTAFTER if you deal with Office 365.

Also, the Excel features Flash Fill or Excel Find and Replace commands will help you to extract the last word in excel. And you have more options to extract the last word in excel using Power Query or Power Pivot. If you need to extract the last word in your automation, you can use either VBA or Office Scripts method.

Suppose you have received the employee details data in a CSV file as below, where it has all the fields combined with a comma delimiter and you need to extract only the country from the record and placed it in an adjacent column for your report.


Get your copy of the example workbook used in this blog post to follow along!

Extract Last Word in Excel with Basic functions

In this method, you are going to use four basic excel in-built functions TRIM, RIGHT, SUBSTITUTE and REPT to extract the last word in excel.

TRIM( text )

TRIM function removes all spaces except for single spaces between words.

RIGHT( text, [num_chars] )

RIGHT function will return the last specified number of characters from a text string.

SUBSTITUTE( text, old_text, new_text, [instance_num] )

SUBSTITUTE function to replace specific text in a text string.

REPT( text, number_times )

Use the REPT function to fill a cell with multiple instances of the same text string, repeating it the specified number of times.

Please follow the below steps to extract the last word Country in the record.

Go to cell B2, type the formula =TRIM( RIGHT( SUBSTITUTE( D3, ",", REPT( " ",100 ) ), 100 ) ) and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

In the above formula, the REPT function repeats the given text blank space 100 times. SUBSTITUTE function substitutes each instance of the comma delimiter with the 100 blank spaces.

The RIGHT function returns the last 100 characters and finally TRIM  function removes all the blank spaces and returns only the Country text.

Extract Last Word with Excel 365 functions

You now have access to two new functions, TEXTSPLIT and TEXTAFTER, in Excel 365 and Excel for the web which can be used to extract the last word in a cell. These functions will not work in the earlier versions of excel.

Using TEXTSPLIT Function

=TEXTSPLIT( text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with] )

The TEXTSPLIT function works the same as the Text-to-Columns wizard. Splits text strings by using column and row delimiters.

INDEX( array, row_num, [column_num] )

INDEX returns a value from a table or range.

Please follow the below steps to extract the last word Country from the data using INDEX and TEXTSPLIT functions.

Go to cell B2, type the formula =INDEX( TEXTSPLIT( A2, "," ), COUNTA( TEXTSPLIT( A2, "," ) ) ) , and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

TEXTSPLIT function split the text by using a comma delimiter and returns an array of words. COUNTA returns the total number of words in the array returned by TEXTSPLIT. INDEX function returns the last word in the list of words returned by the TEXTSPLIT function.

Using TEXTAFTER function

You can use the TEXTAFTER function to extract the last word in excel. Compare with all other approaches it is very easy and simple to use.

= TEXTAFTER( text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] )

It returns the text that comes after a given character or string. Assign -1 to the optional argument instance_num, since you need to extract the last word country.

Follow the below steps to extract the last word country using the TEXTAFTER function.

Go to cell B2, type the formula =TEXTAFTER( A2, ",", -1 ), and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

TEXTAFTER splits the record by comma delimiter and returns only the last word country.

Extract Last Word in Excel with Flash Fill

Flash Fill is a feature in Excel that automatically completes data entry tasks based on patterns that you enter. It is a time-saving tool that can help you quickly enter repetitive data into an organized format.

You can start entering the country name in column B in cells B2 and B3. Excel will fill in the rest of the data for you. You just press Enter to fill in the values.

Extract Last Word in Excel with Find and Replace

You can use Excel Find and Replace feature to extract the last word.

This will change the data, so it’s best to create a copy of the source data first to use the find and replace feature on.

Please follow the below steps to extract the last word country.

  1. Copy and paste all the records in column B and Select the range B2:B11 
  2. Select the Home tab.
  3. Choose Find & Select.
  4. Select the Replace menu item to open the Find and Replace dialog box.
  1. Type *, in the Find what: input box comma is a delimiter in this example.
  2. Just leave blank the Replace with: input box.
  3. Press the Replace All button.

The * character will act as a wildcard in the find and replace. This means everything to the left of a comma will be replaced with whatever you add to the Replace with field.

Since this is left blank, everything except the last word will be removed.

Extract Last Word in Excel with Power Pivot (DAX)

Power Pivot allows you to easily process the data and create complex data modelling features including relationships, hierarchies and calculated columns, allowing you to examine the data.

  1. Select the range of cells A1:A11
  2. Choose  Add to Data Model in the Power Pivot Menu– to open the Power Pivot editor
  1. Rename the Second column Country
  2. Type the formula =TRIM( RIGHT( SUBSTITUTE( [Employee Details], ",", REPT( " ", 100 ) ), 100 ) ) in the formula bar and Press Enter.
  1. Rename the PowerPivot Table name PPData
  2. Select Home Tab
  3. Choose Pivot Table Icon
  4. Select Flattened PivotTable – to open Create Flattened PivotTable
  1. Choose Existing Worksheet
  2. Select cell C1 in the Location Input box, where you need to place the data.
  3. Press OK

The above steps add an empty pivot table to your worksheet cell C1.

Drag both the fields Employee Details and Country into the Rows in PivotTable Fields.

Extract Last Word in Excel with Power Query

Power Query is a powerful data processing and transformation tool that enables you to connect to a wide range of data sources, transform, integrate, and shape the data, and bring it into Excel. Power Query may be used to load data from a variety of sources, including databases, files, web pages, and folder as well as build complicated queries using a graphical user interface.

Steps to extract the last word using Power Query

  1. Select the range of cells A1:A11
  2. Choose From Table/Range option in Data Menu– to open the Power Query window
  1. Go to Transform
  2. Select Split Column Icon
  3. Choose By Delimiter option – to open Split Column by Delimiter dialog box
  1. Select the Right-most delimiter option in the Split at section and Press OK

The above steps added a new column, Rename the new column header Country, and update the existing column header.

  1. Select Home Tab
  2. Choose the option Close & Load To… in the Close & Load Menu
  1. Choose the option Table in the Select how you want to view this data in your workbook.
  2. Select the Existing worksheet: and choose the cell, where you need to put the data
  3. Press OK

You can see the output in your worksheet range D1 to E11.

Extract Last Word in Excel with VBA

VBA allows you to extend the functionality of Excel and create custom solutions to problems. It can be used to create custom functions, GUI, automate reports and analysis.

Sub ReturnLastWord ()
'Declare variables
Dim wSheet As Worksheet
Dim tVal As String
Dim rge As Range

'Assign worksheet and selected range to the respective variables
Set wSheet = Worksheets("VBA")
Set rge = ActiveSheet.Range(Selection.Address)

'Loop through the Selected range of cells
For Each rg In rge
    'Assign the current cell value to the variable tVal
    tVal = rg.Value
    
    'Put the extracted word to the adjacent cell
    rg.Offset(0, 1) = Right(tVal, Len(tVal) - (InStrRev(tVal, ",")))
Next
End Sub

In the VBE, add the above code to your module.

To Run the ReturnLastWord macro

Select the range of cells A2:A11, and Press Alt + F8, the keyboard shortcut to open the Macro dialog box. Choose the ReturnLastWord Macro and then Press Run.

Macro assigns the worksheet and the selected range to the respective variables. The code loop through the selected range.  In each iteration, the current cell value assigns to the variable tVal, and compute the formula to extract the last word. Finally, the extracted word is put in an adjacent cell.

Eg: The first record – Cade Roach,(233) 626-3282,metus.aenean@hotmail.net,France

InStrRev(tVal, “,”) – returns  51, which is the position of last comma in the example

Len(tVal) – returns 57, The total number of characters in the record

Right – returns France last 6 characters from the record.

Extract Last Word in Excel with Office Script

Office Scripts is a programming script used for creating macros and automate repetitive or common tasks by recording and running macros in Microsoft Excel.

function main(workbook: ExcelScript.Workbook) {

  //getselected range
  let rng = workbook.getSelectedRange();
  let rows = rng.getRowCount();

//loop through the selected range of cells
  for (let i = 0; i < rows; i++) {

    //Assign the cell value to the variable cellValue as string
    let cellValue = rng.getCell(i, 0).getValue() as string

    //Split the sentence by delimiter and count the number of words
    let numberOfWords = cellValue.split(",").length as number

    // set the extracted word in the adjacent column
    rng.getCell(i, 1).setValue(cellValue.split(",")[numberOfWords-1])

  }
}

The script assigns the selected range and the number of rows in the respective variables. Code loop through the number of rows. Assigned current value as string to the variable cellValue. The split function split the records by the delimiter comma, length property returns the total number of words and stores it in the variable numberOfWords. Finally, put the extracted word into the adjacent cell.

Follow the below steps to execute the Extract Last Word Office script in your excel

  1. Select the range of cells A2:A11
  2. Go to Automate menu
  3. Select the Extract Last Word Script – to open the Code editor on the right side of the excel window
  4. Press Run 

Conclusions

The most common file format you will receive is a Comma Separated Value (CSV) file. It is often necessary to split or extracted the records into multiple columns in excel for further analysis. You learned 9 different methods to extract the last word in excel.

Flash Fill is a great option if the data only needs to be split once. This method is simple and can be completed in a few steps. A formula solution is the best option if the data needs to be updated dynamically. You learned three different approaches to extracting the last word in excel using functions, which are depending on the version of Excel being used. If you are working with Excel 365, TEXTAFTER is a very simple formula to extract the last word.

Additionally, to work with larger datasets or external datasets, Power Query or Power Pivot are the best options to use. Finally, VBA or Office scripts are useful methods If you need to extract the last word in your automaton or dashboard.

Do you know any other method to Extract Last Word in Excel? Let me know in the comments section!

Similar Posts