Related Functions:

The MID function returns a specified number of characters from the middle of a supplied text string.

Syntax

=MID(text,start_num,num_chars)

=MIDB(text,start_num,num_bytes)

Arguments

Argument Description
text The original text string
start_num Specifies the position of the first character that you want to be returned
num_chars Specifies the number of characters you want MID to return from text
num_bytes Specifies the number of characters you want MIDB to return from text, in bytes

Note: The MID & MIDB functions always returns a text string, even though this may be contain digits and therefore may look like a number. This should be borne in mind if you wish to use the result of the function within further formulas.

Examples

  A B C D E F
1 Text Start Chars Formula Result Notes
2 The cat in the hat 5 3 =MID(A2,B2,C2) cat Retrieves characters 5 – 7
3   16 3 =MID(A2,B3,C3) hat Retrieves characters 16 – 18
4 string_unwanted 1   =MID(A4,B4,FIND(“_”,A4,2)-1) string Uses FIND with MID to remove “_xxxx”
5 string_garbage 1   =MID(A5,B5,FIND(“_”,A5,2)-1) string Same as above
6 256 years 3 1 =MID(A6,B6,C6) 6 Although the text string contains a number, this is stored as a text string in Excel.

Usage note: Use the MID or MIDB function when you want to extract text from inside a text string, based on location and length.

  • Use FIND or SEARCH to locate start_sum when you don’t know the location in advance.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the supplied start_num argument is < 1
  the supplied num_chars or num_bytes argument is < 0