What does it do?
The Excel MID function allows you to extract a portion of text from a text string contained in a single cell. By specifying the starting position and the length, you can extract any numbers of characters from within. In addition to letters and numbers, special characters (not limited to), asterisks, question marks, logical operators and spaces etc., are included as part of the extraction. Like the Excel RIGHT, LEFT and LEN functions, the Excel MID function factors in the characters in the cell value only, so any display values like currency codes and date formats are ignored.
Syntax Explained
Text: This is the text string where characters will be extracted from.
Start_num: This is the position where the extraction should begin. Entering 1 would represent the first character in the text string.
Num_chars: This is the number of characters to extract from the text string. If it is left blank, it will default to 0, this resulting in an empty return.
Examples
1. MID covering many types.
In the example above I have set up a few text strings where we can extract a number of characters from:
- Row 3 = Attempts to extract “message” and succeeds, starting position is 11, this includes spaces, the word “message” is 7 letters long.
- Row 4 = As above but also includes the exclamation mark.
- Row 5 = Extracts the characters including the decimal point, note the cell format is set to show a “BTC” or Bitcoin sign.
- Row 6 = Extracts from the date value only. All dates in Excel are made up of a 5-digit number, the dd/mm/yyyy format is a cell display only.
- Row 7 = Extracts only from the cell value only. The Cell display is set to hh:mm:ss format so this is ignored.
Pro Tip
Combining, or using alternatives to the MID functions such as SEARCH, FIND, LEN, RIGHT and LEFT can help support text extraction techniques. When used correctly they can make light work of something that would take hours/days using manual methods.