Excel: How to Extract Text Right of Colon

You can use the following formula to extract all of the text to the right of a colon in some cell in Excel:

=TEXTAFTER(A2, ":")

This particular formula extracts all of the text in cell A2 that occurs after the first colon is encountered.

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

Example: Extract Text Right of Colon in Excel

Suppose we have the following list in Excel that shows the athlete name and team name of various basketball players:

Now suppose that we would like to extract only the text in each cell to the right of the colon.

To do so, we can type the following formula into cell B2:

=TEXTAFTER(A2, ":")

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

Excel right of colon

Column B now displays only the text to the right of the colon of each cell in column A.

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 to the right of the colon from cell A2:

=TEXTAFTER(A2, ":")

By using “:” for the delimiter argument, we were able to specify that we wanted to extract the text after the first instance of a colon.

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

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