You can use the following syntax to extract the text after the last comma in a particular cell in Excel:
=TEXTAFTER(A2, ",", -1)
This particular formula extracts the text after the last comma in cell A2.
The following example shows how to use this formula in practice.
Example: Extract Text After Last Comma in Excel
Suppose we have the following dataset in Excel in which column A contains a description of basketball players including their team, position, and classification:
Suppose we would like to extract the text after the last comma in the cells in column A.
We can type the following formula into cell C2 to do so:
=TEXTAFTER(A2, ",", -1)
We can then click and drag this formula down to each remaining cell in column C:
Column C now contains the text in column A after the last comma.
For example:
- The formula extracts Good from Mavs,Guard,Good
- The formula extracts Great from Mavs,Forward,Great
- The formula extracts Bad from Mavs,Forward,Bad
And so on.
How This Formula Works
The TEXTAFTER function in Excel extracts all text in a cell after a specific character or substring.
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 comma 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 comma.
Note: You can find the complete documentation for the TEXTAFTER function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Remove Specific Text from Cells
Excel: How to Remove Special Characters
Excel: How to Insert a Character into a String
