Excel MID Function to extract substring

This Excel tutorial explains how to use Excel MID Function to extract substring from a string, and use with FIND and SEARCH and LEN Function.

You may also want to read:

Extract text in brackets

Remove text in brackets

Excel MID Function

MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string.

MID Function is commonly used with FIND Function or Search Function, both of them are used to return the position of a substring (In VBA, use INSTR Function). For example, I want to extract the last name of a person (Gilbert) from the full name (Peter, Gilbert), which is separated by a comma. First thing I need to do is to locate the position of the comma using FIND or SEARCH Function, and then extract the last name with MID Function (see the below example).

Syntax of Excel MID Function

MID( text, start_position, number_of_characters )
text The text string from which you want to extract the characters
start_position The position of the first character you want to extract. Positions start at 1.


The number of characters to return.

Example of Excel MID Function

Formula Result Explanation
=MID(“Access Excel.Tips”,1,6) Access Extract substring from position 1 to 6
=MID(“Peter,Gilbert”,1,FIND(“,”,”Peter,Gilbert”,1)-1) Peter Extract substring from position 1 to (comma position-1)
=MID(“Peter,Gilbert”,FIND(“,”,”Peter,Gilbert”,1)+1,LEN(“Peter,Gilbert”)) Gilbert Extract substring from (comma position+1) to (length of whole string)

Outbound References



Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

Your email address will not be published. Required fields are marked *