1. Calculation functions
  2. All Functions
  3. Numeric Functions
  4. MID

Extracts a number of characters from a text string, starting from a character you select.

Syntax

MID(Text, Start position [, Number of characters])
Argument Data type Description
Text (required) Text The text to extract characters from.
Start position (required) Number The numeric starting position in the text string. The extracted characters include the character in the start position you select.
Number of characters (optional) Number The number of characters to extract from the text string.

Returns

The MID function returns a text-formatted result.

Arguments

The Text argument can refer to a text-formatted line item, text constant, or general expression.

The Start position argument can refer to a numeric line item, property, or expression. If you use a Start position that is not present in the text string, the function returns a blank result. If you use a negative number for the Start position argument, the function returns a blank result.

The optional Number of characters argument can refer to a numeric line item, property, or expression. If you omit the Number of characters argument, the default behavior returns the character in the position specified in the Start position argument. If you use a negative number or zero for the Number of characters argument, the function returns a blank result. If the Number of characters argument is greater than the number of characters in the Text argument, the function returns only the characters in the string.

Constraints

The MID function has this constraint:

Excel equivalent

Examples

In this example, a Clothing list displays on columns, with four items, Hats, Shirts, Shorts, and Trousers.

Line items display on rows and are text-formatted. The exception to this is the Clothing List line item, which is list-formatted. The first three line items Red Product, Blue Product, and Yellow Product contain product names. These product line items are used for the Text arguments in the module’s formulas.

A module named Clothing Products. A number of line items display on rows, and four products on columns.

This table shows the formulas and their results for each of the list items Hats, Shirts, Shorts, and Trousers:

Formula Result Description
MID(Red Product, 5, 20)
Hats, Shirts, Shorts, Trousers

The text strings used for the Text argument are Red Hats, Red Shirts, Red Shorts, and Red Trousers.

The formula uses 5 for the Starting position argument, so it extracts the characters from the fifth character onwards.

As 20 is used for the Number of characters argument, the formula returns 20 characters, which is all characters in this case.

MID(NAME(Clothing List), 2, 3)
ats, hir, hor, rou

This formula extracts text from a list-formatted line item.

The MID function can only be used with text-formatted line items. This formula uses the NAME function to convert the list-formatted line item to text-formatted.

MID(Blue Product, (LENGTH(Blue Product) - 7), 5)
lue Hats, e Shirts, e Shorts, Trousers This formula uses the LENGTH function for the Start position argument in combination with the minus operator. This enables you to choose a Start position a chosen number of characters from the end of a text string.

In this example, the line item MID contains the formula:

MID(y, 2, 3) 

This formula extracts three characters from the second character onwards (the Start position argument is inclusive of the character in the position you select). The results in this example follow:

Text string Extracted text
Scarlet car
Pink ink
Yellow ell
Green ree
Orange ran
OLIVE LIV
turquoise urq

Similar functions