Excel: How to Extract Text Between Two Commas


You can use the TEXTBEFORE and TEXTAFTER functions in Excel to extract all text in a cell between two commas.

You can use the following syntax to do so:

=TEXTBEFORE(TEXTAFTER(A2, ","), ",")

This particular example extracts all of the text between the two commas in cell A2.

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

Example: Extract Text Between Two Commas in Excel

Suppose we have the following list of locations in Excel:

Now suppose we would like to extract the state name between the two commas in each cell.

For example, we would like to extract:

  • Ohio from the first cell.
  • Florida from the second cell.
  • Maine from the third cell.

And so on.

We can type the following formula into cell B2 to extract the text between the commas in cell A2:

=TEXTBEFORE(TEXTAFTER(A2, ","), ",")

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

Excel extract text between two commas

Column B now contains the text between the commas for each corresponding cell in column A.

How This Formula Works

Recall the formula that we used to extract the text between the commas in cell A2:

=TEXTBEFORE(TEXTAFTER(A2, ","), ",")

Here is how this formula works:

First, we use the TEXTAFTER function to extract all of the text after the comma in cell A2.

This returns Ohio, United States.

Then, we use the TEXTBEFORE function to extract all of the text before the comma from this remaining text.

This returns Ohio.

Note: If you’d like, you could wrap the TRIM function around this formula to remove any leading or trailing spaces from the resulting text.

Additional Resources

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

Excel: How to Extract Text Before a Character
Excel: How to Extract Text After a Character
Excel: How to Remove Specific Text from Cells

One Reply to “Excel: How to Extract Text Between Two Commas”

  1. Hi Brad … Want me to send a screenshot or the file?

    Using =TEXTBEFORE(TEXTAFTER(A2,” “),” “) to extract string between the 1st and 2nd space. BUT 2 questions
    1. If there’s no middle name iI displays #N/A. How do I leave cell blank if there’s no middle name?
    2. When the name has 4 or more strings eg “Mary Alice Fitzhugh Smith” MIDDLE extracts “Alice” so how do I get it to extract “Alice Fitzhugh” ie text between the 1st and LAST space?

Leave a Reply

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