Table of Contents
The LEFT function in Excel can be used to extract a substring from a larger string, before a certain character or delimiter. To use the LEFT function to extract text before a comma, just supply the text in the first argument of the function, followed by the number of characters you want to extract in the second argument, and a comma as the third argument. This will cause the LEFT function to return the desired substring from the original text.
You can use the following formula with the LEFT and FIND function to extract all of the text before a comma is encountered in some cell in Excel:
=LEFT(A2, FIND(",", A2)-1)
This particular formula extracts all of the text in cell A2 that occurs before the first comma is encountered.
The following example shows how to use this formula in practice.
Example: Using LEFT to Extract Text Before Comma
Suppose we have the following list in Excel that provides a brief description of different basketball players including their team, position, and ranking:
Now suppose that we would like to extract only the team name for each player.
To do so, we can use the following formula with the LEFT and FIND functions in Excel to extract the text from the left side of each cell until a comma is encountered:
=LEFT(A2, FIND(",", A2)-1)
We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:
Column B now displays only the team name for each player in column A.
It’s worth noting that if no comma is found in the Player Description column, the formula will return #VALUE! as a result.
To return a different value, simply use the IFERROR() function.
For example, we can use the following IFERROR() function to return “No comma” if a comma is not found in a given player description:
=IFERROR(LEFT(A2, FIND(",", A2)-1), "No comma")
Note that you can also return a different value by simply replacing “No comma” with some other value in the IFERROR() function.