Excel: How to Extract Text Between Two Characters


You can use the TEXTBEFORE and TEXTAFTER functions in Excel to extract all text in a cell between two specific characters or strings.

You can use the following syntax to do so:

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

This particular example extracts all of the text between the characters “this” and “that” in cell A2.

The following examples show some common ways to extract text between two characters in practice.

Example 1: Extract Text Between Strings

We can type the following formula into cell B2 to extract the text in cell A2 between the strings “ran” and “miles”:

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

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

Excel extract text between strings

Column B contains the text between the strings “ran” and “miles” for each corresponding cell in column A.

Example 2: Extract Text Between Parentheses

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

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

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

Excel extract text between parentheses

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

Example 3: Extract Text Between Asterisks

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

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

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

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

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

4 Replies to “Excel: How to Extract Text Between Two Characters”

  1. Hi Zach,

    How to pull out specific data and separate it in columns as below

    Before
    NO. K/P : 123456-12-1234 / a12345
    NO. K/P : a12345 / 123456-12-1234

    After
    123456121234 a12345
    a12345 123456121234

  2. Hello,
    The scenario I have contains multiple instances of the “delimiter”, a number in decimal form needs to be added, but in fraction form, along with some other terms being moved around.
    This is how the data appears:
    Cell C2: 0.5 (want in fraction form. I’ve used: text(c2,”##-##/##”)&””” ”
    This is the bulk of the Description:
    Cell D2:
    API 602 EQ./6D, CLASS 800, FLOATING BALL VALVE, TH, FULL PORT, A-350 BODY LFC CL1 GRADE, MFTR STD TRIM (TO -50 DEG F MIN), EQ. W/ LOTO DEVICE, LEVER OPERATOR
    Added to the end of the Description:
    Cell F2: Fusion Cell G2: 334TF005F30L2AAAF
    This is the desired end result:
    VALVE, FLOATING BALL, 1/2″ CLASS 800, TH, FULL PORT, A-350 BODY LFC CL1 GRADE, MFTR STD TRIM (TO -50 DEG F MIN), EQ. W/ LOTO DEVICE, LEVER OPERATOR , API 602 EQ./6D, FUSION 334TF005F30L2AAAF

Leave a Reply

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