The MID formula with example in Excel

The MID function in Excel is used to extract a specific number of characters from a text string, starting at a specified position. This function is particularly useful when you need to pull out substrings from a larger string of text.

Syntax

MID(text, start_num, num_chars)

• text: The text string from which you want to extract characters.
• start_num: The position of the first character you want to extract. The first character in the text string is position 1.
• num_chars: The number of characters you want to extract.

Example

Suppose you have a list of product codes, and you want to extract a specific part of the code.

Data

A
PROD-001
PROD-002
PROD-003
PROD-004

You want to extract the numeric part of each product code.

Steps

1. Enter the product codes in column A (A1

   ).

2. Use the MID function to extract the numeric part of the product code. In cell B1, enter the following formula:

   =MID(A1, 6, 3)
   

3. Copy the formula from B1 down to B4.

Explanation of the Formula

• A1 is the cell containing the text string "PROD-001".
• 6 is the starting position of the numeric part in the text string.
• 3 is the number of characters to extract.

Result

A	B
PROD-001	001
PROD-002	002
PROD-003	003
PROD-004	004

The MID function extracts the numeric part from each product code.

Another Example with Different Lengths

Suppose you have a list of mixed-length text strings and you want to extract a specific part of each string.

Data

A
ABCDEFGH
IJKLMNOP
QRSTUVWXYZ
1234567890

You want to extract the 3 characters starting from the 4th position of each string.

Steps

1. Enter the text strings in column A (A1).

2. Use the MID function to extract the 3 characters starting from the 4th position. In cell B1, enter the following formula:

   =MID(A1, 4, 3)
   

3. Copy the formula from B1 down to B4.

Explanation of the Formula

• A1 is the cell containing the text string "ABCDEFGH".
• 4 is the starting position in the text string.
• 3 is the number of characters to extract.

Result

A	B
ABCDEFGH	DEF
IJKLMNOP	JKL
QRSTUVWXYZ	TUV
1234567890	456

The MID function extracts the specified part from each text string based on the provided starting position and number of characters to extract. This function is very handy for manipulating and analyzing text data in Excel.