How can I extract the text after the last space in Excel?

By /

The process of extracting the text after the last space in Excel involves using the RIGHT function, which allows you to specify the number of characters you want to extract from the right side of a text string. By utilizing this function and specifying a number that is larger than the length of the text string, you can extract the text after the last space. This can be useful for separating first and last names, or for extracting specific data from a longer string of text. Simply input the desired formula into a cell and specify the text string you want to extract from, and the result will be the text after the last space. This can be a useful tool for organizing and analyzing data in an Excel spreadsheet.

Excel: Extract Text After Last Space

You can use the following syntax to extract the text after the last space in a particular cell in Excel:

=TEXTAFTER(A2, " ", -1)

This particular formula extracts the text after the last space in cell A2.

The following example shows how to use this formula in practice.

Example: Extract Text After Last Space in Excel

Suppose we have the following column of strings in Excel:

Suppose we would like to extract the text after the last space in each cell in column A.

We can type the following formula into cell B2 to do so:

=TEXTAFTER(A2, " ", -1)

We can then click and drag this formula down to each remaining cell in column B:

Excel extract text after last space

Column B now contains the text in column A after the last space.

For example:

  • The formula extracts manatee from My favorite animal is a manatee
  • The formula extracts day from This is a great day
  • The formula extracts probably from He love basketball, probably

And so on.

How This Formula Works

This function uses the following syntax:

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

where:

  • text: Text to search
  • delimiter: Character or substring to extract text after
  • instance_num (optional): Instance of delimiter after which to extract text (default is 1)
  • match_mode (optional): 0 = case-sensitive (default), 1 = case-insensitive
  • match_end (optional): Treat end of text as delimiter (disabled by default)
  • if_not_found (optional): Value to return if delimiter is not found

Recall that we used the following syntax to extract the text after the last space in a cell:

=TEXTAFTER(A2, " ", -1)

By using a value of -1 for the instance_num argument, we were able to specify that we wanted to extract the text after the last instance of a space.

Note: You can find the complete documentation for the TEXTAFTER function in Excel .

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. Excel: Extract Date from Text String How do i extract a date from a text string?
  2. How can I extract text to the right of a space in Excel?
  3. Excel: Extract Text Right of Space
  4. How do I extract text after the last space in a cell in Excel?
  5. How do I extract text between second and third space in Excel?
  6. How do I use LEFT to extract text before space in Google Sheets?
  7. How can I split text by space in Excel?
  8. How do I add a space between text in a cell in Excel?
  9. How can I extract characters from the right in Excel until the first space?
  10. Split Text by Space in Excel (With Example)