Excel: Use LEFT to Extract Text Before Comma


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:

Excel formula for LEFT of comma

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")

The following screenshot shows how to use this formula in practice:

Note that you can also return a different value by simply replacing “No comma” with some other value in the IFERROR() function.

Additional Resources

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

Excel: How to Use MID Function to End of String
Excel: How to Use MID Function for Variable Length Strings
Excel: How to Remove First 3 Characters from String

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *