How to Extract Part of Text in Excel Cell use MID Function

The MID function (for single-byte encodings) displays a specified number of characters from a text string, starting from a given position. The same task is solved by the MIDB function, but it works with double-byte characters (returns a specified number of bytes).



Features and Syntax

Each character for the function is considered one symbol, regardless of the encoding type. For the MIDB function, a double-byte character counts as two symbols (if double-byte encoding is enabled). This language is the default. Double-byte encoding is supported by Japanese, Chinese, and Korean languages.

Let’s return to the MID function. Its syntax is:

MID function Example
  1. Text string. The text with the characters the function needs to extract.
  2. Specified position. The starting position of the specific character in the text from which the count will begin. The position of the first extracted character is 1.
  3. Number of characters. How many characters the function needs to return.

All arguments are mandatory.

Function operation features:

  1. If the number of characters for the “Starting position” argument exceeds the length of the text string, the result will be an empty string.
  2. If the value of the “Starting position” argument is less than one, the error #VALUE! will return.
  3. If the “Number of characters” argument has a negative value, the function returns the error #VALUE!.

The MID function in Excel is designed to work with text data.

Examples of the MID Function in Excel

Let’s look at how the MID function works with a typical example.

Data:

Data.

Formula Example 1:

Formula.

Process Description:

  1. The MID function extracts a substring from a string, starting from a specified position.
  2. Function arguments:
  • A1 – the original text "MID Function";
  • 1 – the starting position (the letter M);
  • 3 – the number of characters to extract (MID consists of 3 letters).

As a result, B1 will get the text MID. Thus, the formula extracts the desired part of the string, starting from the specified position and length.

Formula Example 2:

MID.

It returns 4 characters from cell A1 (the first argument), starting from the ninth character (the second argument). Spaces are also counted. 9 is the starting position from which the function will take the characters. In the example, we manually counted the number of characters: “function” - 7 characters + 1 space. In total: 8 characters.

Process Description:

  1. The function allows you to extract part of the text based on certain conditions.
  2. Function arguments:
  • A1 – the original string "MID Function";
  • 5 – the starting position (the first letter F in "Function");
  • 8 – the number of characters in the word "Function".

Let’s consider the practical use of the function.

Source.

From the list of products, we need to extract the collection names. For this purpose, this formula is ideal.

Apply the formula:

Gino.

Let’s assume the number of characters is unknown in advance. Or, as in our example, the number of characters before the desired value differs. In this case, MID is used in conjunction with the FIND function.

Example.

How it works:

  1. FIND("(", A1) + 1 – determines where the collection name starts (right after the first opening parenthesis "(").
  2. FIND(" ", A1, FIND("(", A1)) – finds the first space after the first parenthesis "(", which marks the end of the collection name.
  3. FIND(" ", A1, FIND("(", A1)) - FIND("(", A1) - 1 – calculates the length of the word (collection name).
  4. MID(A1, start_position, number_of_characters) – extracts only the collection name.

The FIND function helps to determine the number of characters based on some condition.

Let’s assume the numbers in the example represent product prices, not article numbers.

Products.

We extracted the necessary values – the prices. But summing them in the usual way doesn't work.

SUM.

The SUM function doesn't recognize the values obtained using the MID function, as the numbers are in text format. To convert them into numbers, we need to apply the VALUE function to the result.

VALUE.

Download Example of How to Extract Part of Text in Excel download file

Thus, MID is part of the group of text functions in Excel and returns a specified number of characters from a given string, starting from a specified position. In practice, it is rarely used alone, as it is difficult to accurately specify the number of characters in the entire range for the function’s arguments.