Microsoft Excel Tips and Tricks - Computergaga

Excel MID Function

The Excel MID function extracts characters from the middle of a text string.

While the LEFT and RIGHT worksheet functions in Excel are used to extract a specified number of characters from either end of a string, MID extract them from within the string.

The MID function in Excel requires three arguments.

The syntax for the MID function is:

=MID(text, start_num, num_chars)

  • Text: The text string you need to extract characters from.

  • Start_num: The index position of the first character in the text string you want to extract.

  • Num_chars: The number of characters to extract.

Download the sample workbook to practise.

How to Use the Excel MID Function

The MID function in Excel extracts characters from the middle of string when given three pieces of information – the text, the starting position, and the number of characters to extract. For example, the MID formula =MID(A2,3,2) extracts two characters from the third character of the string in cell A2.

Let’s look at some MID formula examples.

MID Function with Fixed First Character

In this first MID formula example, the position of the text to extract is in a fixed start position, so a constant can be used for the start_num argument of the MID function.

This formula extracts two characters starting from the seventh character in the string.

=MID(A2,7,2)
MID formula to extract characters from a regular text string

Unfortunately, text strings are not always this regular in length and pattern. So, the MID function is often combined with the FIND or SEARCH function in Excel to locate the middle part.

Advanced Excel Formulas book

MID Function with Irregular First Character

For this MID formula example, the starting position of the first character is irregular.

The SEARCH function is used to return the index number of the character preceding the characters we want to extract. In this example, that is the hyphen “-” character.

One is then added to this index to return the position of the first character in the string we want to extract.

MID returns the two characters from that point, as the text length to return is a fixed number of characters.

=MID(A2,SEARCH("-",A2)+1,2)
MID function example with SEARCH for irregular start number

Complex Excel MID Function Example

In this MID formula, the middle part of the text string is irregular in length and its start position.

Again, SEARCH is used to assist the MID function in Excel. This time, there are three instances of the SEARCH function.

It is used to return the start of the text to extract (first character after the open bracket), and to calculate the number of characters to extract. The index number of the open bracket is subtracted from the index number of the close bracket, then another one is subtracted to find the text length.

=MID(A2,
SEARCH("(",A2)+1,
SEARCH(")",A2)-SEARCH("(",A2)-1
)
MID formula with irregular number of characters and start index

For complex examples such as this, the TEXTBEFORE and TEXTAFTER functions in Excel are often better alternatives, although they are only available in Excel 365.

In-fact, due to the more complex nature and the repetition of the SEARCH function in this MID formula, a similar example is used in our LAMBDA function tutorial.

What is the MID Function in Excel for Date?

When a date is stored as text in Excel, you can use the MID function to extract the date elements. With a date in the format of dd/mm/yyyy you can use the following instances of the MID formula

  • =MID(A2,1,2) for the day.

  • =MID(A2,4,2) to extract the month.

  • =MID(A2,7,4) to extract the year.

MID function in Excel for the date

This MID formula only works if the date is stored as text. A date is stored as a serial number in Excel, so the 7th September 2023 is stored as the value 45176. The MID text function to return the day would return 45.

There are, of course, methods in Excel to convert text strings to date values. Converting to a date would be better if you are planning on further analysis. However, it is a nice MID function example.

MID with ISO Date as Text String

If the date is structured in the ISO format of yyyymmdd, the following formulas use the MID function in Excel to extract the different parts.

  • =MID(A2,1,4) for the day.

  • =MID(A2,5,2) for the month.

  • =MID(A2,7,2) for the year.

Use the Excel MID function to extract date elements

Using the DATE function in Excel, the different parts extracted by the MID function can be combined to create a date.

=DATE(
MID(A2,1,4),
MID(A2,5,2),
MID(A2,7,2)
)
DATE function with MID to create a date from a string

Other functions such as LEFT and RIGHT could have been used to extract the year and day elements, as they occur at the ends of the date string. However, it is nice that we can be consistent and use one function, thanks to the the MID function in Excel.

Conclusion

The MID worksheet function is really useful for extracting specific text from a string. In this tutorial we have shown a MID function example with a regular string and a few with irregular strings.

You can learn over 150 Excel functions with my Advanced Excel Formulas book. It includes over 500 formula examples of the best functions in Excel.