Excel: How to Use MID Function for Variable Length Strings


The MID function in Excel allows you to extract a specific number of characters from a string based on a starting position on the left side of the string.

However, sometimes you want to extract middle characters based on a specific starting and ending character.

You can use the MID function combined with the FIND function to do so:

=MID(A2,FIND("char1",A2)+2,FIND("char2",A2,10)-FIND("char1",A2)-2)

This particular formula extracts every character in the string in cell A2 between the characters char1 and char2.

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

Example: MID Function for Variable Length Strings in Excel

Suppose we have the following list of websites URL’s:

Suppose we would like to extract only the website name between the double forward slashes (//) and the .com near the end of the URL.

We can use the following formula to do so:

=MID(A2,FIND("//",A2)+2,FIND(".com",A2,10)-FIND("//",A2)-2)

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

Excel MID function with variable length

Column B now displays only the characters in the website name between the double slashes // and the .com near the end of the name.

Note that this formula works even though the length of each website name is not the same.

Without using the FIND function, the MID function alone would not be able to perform this task.

Additional Resources

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

Excel: A Formula for MID From Right
Excel: A Formula for “If Not Empty”
Excel: How to Ignore Blank Cells When Using Formulas

One Reply to “Excel: How to Use MID Function for Variable Length Strings”

  1. I want to pull the amount of purchase from a cell (with various number of characters into a separate column.
    THE FLYING BISCUIT CAF BROOKHAVEN GA 7536943QVME0EFED2 $41.82!
    TST* HUDSON GRILLE – S SANDY SPGS GA 0230537QV00QP83T4 $10.74
    NUTS N BERRIES ATLANTA GA 7550499D2S66LG7JZ $128.37

    SO – I want to pull $41.82 from first row, $10.74 from second row, and $128.37 from third row.

Leave a Reply

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