The Excel MID Function

Home » Excel-Built-In-Functions » Excel-Text-Functions » Excel-Mid-Function

Search this site:

Custom Search

Related Functions:

LEFT Function

RIGHT Function

Basic Description

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

The format of the function is :

MID( text, start_num, num_chars )

Where the function arguments are:

text	-	The original text string.
start_num	-	An integer that specifies the position of the first character that you want to be returned.
num_chars	-	An integer that specifies the number of characters (beginning with start_num), to be returned from the supplied text.

The Mid function 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 functions and formulas.

Mid Function Examples

The spreadsheet below shows three examples of the Mid function.

The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

Formulas

Examples of use of the Excel Mid Function

Results

Note that the example in cell B3 returns the text value "5". Although the text string contains a number, this is stored as a text string in Excel. Excel Data Types are explained further on the Excel Formatting page.

Further information and examples of the Excel Mid function are provided on the Microsoft Office website.

Using the Excel Mid Function with Dates

Common Problem

Some users make the mistake of attempting to use the , Left, Mid or Right functions on dates.

Dates are stored in Excel as numbers, and it is only the cell formatting that makes them appear as dates in your spreadsheet. Therefore, if you attempt use the Left, Mid or Right function on a date, the function will return the end characters of the number that represents that date.

For example, 01/01/1980 is represented by the number 29221, so applying the Mid function to a cell containing the date 01/01/1980 (and requesting that 2 characters, starting from the 2nd character are returned) would result in a returned value of "92".

Solution No. 1

Use the Day, Month or Year functions to extract individual parts of a date.

Solution No. 2

If you are not using the dates in other calculations, which rely on them being stored as numbers, you can convert the cells containing dates to text, using Excel's Text To Columns tool. To do this:

Use the mouse to select the cells you want to convert to text (this must not span more than one column)
From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
Ensure the Delimited option is selected and click next
Ensure all the Delimiters are unchecked and click next
You should now be offered a selection of Column Data Formats. Select Text and click the Finish button

The data in your selected cells should now be stored as text within Excel, and the Mid function will work as expected.

Return to the Excel Text Functions Page

Return to the List of All Built-In Excel Functions

Disclaimer

Privacy Policy