MID

Returns text from the middle of a text string.

Syntax:

MID(text, start, number)

returns number characters from the text text, starting at position start.

Example:

MID("output", 3, 2)

returns tp.

Application:

Let's imagine you are an analyst for a large company, and you have a list of product IDs in a spreadsheet. The product ID is structured as follows: [Region Code]-[Product Type Code]-[Unique Identifier].

You need to extract the "Product Type Code" for each product to perform an analysis on the types of products being sold. The Product Type Code is always a 4-character code located in the middle of the string.

	Product ID	Region Code	Product Type Code	Unique Identifier
	A	B	C	D
1	NA-ELEC-7890	NA	ELEC	7890
2	EU-FASH-1234	EU	FASH	1234
3	AS-HOME-5678	AS	HOME	5678
4	NA-TECH-9012	NA	TECH	9012
5	EU-AUTO-3456	EU	AUTO	3456

Extracting the Region Code

The Region Code is always the first two characters of the string. To extract this using MID, you need to specify a starting position of 1 and a length of 2.

Formula: MID(A1, 1, 2)

A1: The cell containing the full product ID.
1: The starting position. The Region Code starts at the very beginning of the string.
2: The number of characters to extract. The Region Code is always two characters long.

Result for "NA-ELEC-7890": NA

Extracting the Product Type Code

The Product Type Code is a four-character code that is consistently located in the middle of the string. It starts after the hyphen following the Region Code.

Formula: MID(A1, 4, 4)

A1: The cell containing the full product ID.
4: The starting position. The Product Type Code begins at the fourth character of the string (e.g., in "NA-ELEC-7890," the 'E' of "ELEC" is the fourth character).
4: The number of characters to extract. The Product Type Code is always four characters long.

Result for "NA-ELEC-7890": ELEC

Extracting the Unique Identifier

The Unique Identifier is a four-digit number at the end of the string. To extract this using MID, you need to specify a starting position that accounts for the preceding parts.

Formula: MID(A1, 9, 4)

A1: The cell containing the full product ID.
9: The starting position. The Unique Identifier starts after the second hyphen. The count is: N (1), A (2), - (3), E (4), L (5), E (6), C (7), - (8). Therefore, the '7' of "7890" is the ninth character.
4: The number of characters to extract. The Unique Identifier is always four characters long.

Result for "NA-ELEC-7890": 7890

This demonstrates how the MID function, by changing its start position and number of characters, can be used to isolate and extract different segments from a structured text string.