Extract characters from within a text string.

MID(text, start_num, num_chars), MIDB(text, start_num, num_bytes)

---

Extract characters from within a text string or number using either defined values (e.g. "string") or cell references (e.g. C3).

MID syntax requires the string from which to extract the characters, the position of the first character (or byte) to be extracted and the number of characters or bytes to extract from the string.

---

MID with a text string.

In the example below, the MID function is used to extract characters from within a word and number, both formatted as text strings. Note the combination with the FIND function to locate the position of the first character to be extracted.

---

MID with a number.

In the example below, the MID function is used to extract numbers from within a defined number and a cell reference.

---

Syntax

The MID and MIDB functions consists of 3 arguments:

1. text. Required. The string from which to extract the characters.
2. start_num. Required. The position of the first character (or byte) to be extracted.
3. num_chars or num_bytes. Required. The number of characters or bytes to extract from the string.

---

Try it now!

Enter your formulas into the grey cells.

---

Scope

MID() and MIDB() can be applied to cell references, text or numbers.

Cell reference:	=MID(C3,6,4)
Text:	=MID("South East Asia",6,4) || returns East.
Number:	=MID(123456,4,3) || returns 456.

Caveats

Language support:

• The MID() and MIDB() functions may not be available in all languages.
  • MID() is intended for use with languages using a single-byte character set.
    • MID() always counts each character (both single- or double-byte) as 1, no matter the language.
  • MIDB() is intended for use with languages using a double-byte character set.
    • MIDB() counts each double-byte character as 2 if the default language supports a double-byte character set, otherwise as a 1.
    • Japanese, Chinese (Simplified), Chinese (Traditional) and Korean support double-byte character sets.

Start_num:

• If start_num is less than 1, MID() and MIDB() return the #VALUE! error.
• If start_num is greater than the length of text, MID() and MIDB() return empty text ("").
• If start_num is less than the length of text but start_num + num_char exceeds the length of text, MID() and MIDB() return the characters up to the end of the text.
• Use FIND() to locate start_num when the position isn't known in advance.

Num_chars:

• If num_chars or num_bytes is negative, MID() and MIDB() return the #VALUE! error.

---

Related Functions

• The LEFT() and RIGHT() functions extract characters from the beginning or end of a string.

---

Functions Category

• Text