Table of Contents
Excel is an incredibly powerful tool that can be used to organize and analyze data of any size. It is an essential tool for businesses, professionals, and students alike, as it allows users to quickly and easily manipulate large amounts of data. Excel is also an incredibly versatile tool, as it allows users to create charts, graphs, and tables, as well as perform complex calculations. It is also capable of extracting text from cells with the use of the “Text to Columns” feature. This feature allows users to quickly and easily separate data into columns based on specified criteria, such as a colon. This article will provide a step-by-step guide on how to extract text right of the colon in Excel.
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:
Column B now displays only the text to the right of the colon of each cell in column A.
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])
- 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 .