How to Extract Text With Excel's MID and MIDB Functions

Extract Text With Excel's MID and MIDB Functions

Startup Stock Photos \ Pixabay 

The MID and MIDB functions in Excel both return a specific number of characters from a text string based on different criteria.

Note: The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

MID or MIDB?

When you have unwanted garbage characters included with your good data or when you only need part of the text string in a cell, Excel has a number of functions that remove the unwanted data.

The function you use depends on where the good data is located relative to the unwanted characters in the cell.

  • If the good data or substring to be kept is on the right side of the data, use the RIGHT function to extract it.
  • If the substring is on the left side of the data, use the LEFT function to extract it.
  • If the substring has unwanted characters on both sides of it, use the MID or MIDB functions to extract it.

Excel MID and MIDB Functions

Extract Good Text From Bad with the MID function

The MID and MIDB functions differ only in the languages they support.

MID is for languages that use the single-byte character set. This group includes most languages such as English and all European languages.

MIDB is for languages that use the double-byte character set. This includes Japanese, Chinese (Simplified), Chinese (Traditional), and Korean. 

The MID and MIDB Function Syntax and Arguments

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the MID function is:

=MID(Text,Start_num,Num_chars)

The syntax for the MIDB function is:

=MIDB(Text,Start_num,Num_bytes)

These arguments tell Excel:

  • The data to be used in the function.
  • The starting position of the good data or substring that is to be extracted.
  • The length of the substring.

Text (required for MID and MIDB function): The text string containing the desired data. This argument can be the actual string or a cell reference to the location of the data in the worksheet.

Start_num (required for MID and MIDB functions): Specifies the starting character from the left of the substring to be kept.

Num_chars (required for MID function): Specifies the number of characters to the right of the Start_num to be retained.

Num_bytes (required for MIDB function): Specifies the number of characters (in bytes) to the right of the Start_num to be retained.

If Start_num is greater than the length of the text string, MID and MIDB return a blank cell. In our example, row 4 shows that Start_num is equal to 14, and the text string is 13 characters long.

If Start_num is less than 1 or Num_chars/Num_bytes is negative, the MID/MIDB function returns the #VALUE! error value. See row 6 of the image, where Start_num is equal to -1.

If Num_chars/Num_bytes references an empty cell or is set to zero, MID/MIDB returns a blank cell. See row 7 of the image, where Num_chars references the empty cell B13.

 

MID Function Example: Extract Good Data from Bad

The example in the image above shows a number of ways to use the MID function to extract a specific number of characters from a text string, including entering the data directly as arguments for the function (row 2) and entering cell references for all three arguments (row 5).

Since it is usually best to enter cell references for arguments rather than the actual data, the information below list the steps used to enter the MID function and its arguments into cell C5.

Use the MID Function Dialog Box

MID function dialog box

Options for entering the function and its arguments into cell C5 include:

  • Typing the complete function =MID(A3,B11,B12) into cell C5. Since Excel Online does not have a Formula tab, this method must be used.
  • Selecting the function and arguments using the function's dialog box.

Using the dialog box to enter the function often simplifies the task as the dialog box takes care of the function's syntax (entering the function's name, the commas separators, and brackets in the correct locations and quantity).

No matter which option you choose for entering the function into a worksheet cell, it is probably best to use point and click to enter any and all cell references used as arguments to minimize the chance of errors caused by typing in the wrong cell reference.

  1. Select cell C1 to make it the active cell. This is where the results of the function will be displayed.
  2. Select Formulas.
  3. Choose Text to open the function drop-down list.
  4. Select MID in the list to bring up the Function Arguments dialog box.
  5. In the dialog box, place the cursor in the Text line.
  6. Select cell A5 in the worksheet to enter this cell reference as the Text  argument.
  7. Place the cursor in the Start_num line.
  8. Select cell B11 in the worksheet to enter this cell reference.
  9. Place the cursor in the Num_chars line.
  10. Select cell B12 in the worksheet to enter this cell reference.
  11. Select OK to complete the function and close the dialog box.

The extracted substring file #6 appears in cell C5.

When you select cell C5 the complete function =MID(A3,B11,B12) appears in the formula bar above the worksheet.

 

Extract Numbers with the MID Function

As shown in the row eight of the example above, the MID function extracts a subset of numeric data from a longer number using the steps listed above.

The only problem is that the extracted data is converted to text and cannot be used in calculations involving certain functions such as the SUM and AVERAGE functions.

One way around this problem is to use the VALUE function to convert the text into a number as shown in row 9 above:

=VALUE(MID(A8,5,3))