Excel MID function

Summary

The Excel MID function extracts a specific substring from a text string, starting at the position you specify, and returns the specified number of characters.
Syntax
				
					=MID(text, start_num, num_chars)
				
			
  • text: The text string from which to extract the substring
  • start_num: The position of the first character you want to extract in the text string
  • num_chars: The number of characters to extract
Return value
A substring of the specified length starting from the specified position in the text string.

How to use

MID is used for text parsing when you need to extract specific information from inside a text string. This function is particularly useful in data cleaning, manipulation, and when dealing with structured text formats like codes or IDs.

Examples

Simple MID
Extracting a Substring from Text: To extract a substring from a specific position:
				
					=MID(A1, 4, 5)
				
			
If A1 contains “ExcelFunction”, this formula returns “elFun”.
MID with Cell References
Dynamic Substring Extraction: Extracting a substring based on dynamic starting positions:
				
					=MID(B2, 2, LEN(B2) - 2)
				
			
This removes the first and last characters from the text in B2.
MID with FIND for Dynamic Extraction
Extracting Data After a Specific Character: Combining MID with FIND to extract a dynamic substring:
				
					=MID(E2, FIND(":", E2) + 1, LEN(E2) - FIND(":", E2))
				
			
Extracts the text after the first colon in E2.
MID with SEARCH for Substring Extraction
Extracting Text Between Parentheses: Combining MID with SEARCH to dynamically extract a substring between parentheses:
				
					=MID(F2, SEARCH("(", F2) + 1, SEARCH(")", F2) - SEARCH("(", F2) - 1)
				
			
This formula finds the position of the first parenthesis in F2, then extracts the text between the first “(” and the next “)”. For example, if F2 contains “Result (123)”, the formula returns “123”.

Additional Notes

  • MID is useful for extracting data from strings where the position of the desired text is known or can be determined.
  • For extracting characters from the start or end of a string, use LEFT or RIGHT respectively.
  • MID is often used in combination with FIND or SEARCH for more dynamic substring extraction.

Related Functions

Excel RIGHT function

The Excel RIGHT function extracts characters from the end of a text string, ideal for isolating specific data segments.

Excel LEFT function

The Excel LEFT function extracts a specified number of characters from the start of a text string, useful for data parsing.

Excel LEN function

The Excel LEN function returns the length of a text string in characters, aiding in text data analysis and validation.

Content Navigation