This tutorial explains how to use MID Function to extract substring from a string in Excel / Access VBA
Excel / Access VBA 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 INSTR Function, which returns the position of the string (In Excel worksheet, use Search Function or Find Function instead of 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 INSTR Function, and then extract the last name with MID Function (see the below example).
Syntax of VBA 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. |
number_of_character |
The number of characters to return. |
Example of VBA Excel MID Function
Formula | Result | Explanation |
MID(“Access Excel.Tips”,1,6) | Access | Extract substring from position 1 to 6 |
Mid(“Peter,Gilbert”, 1, InStr(1, “Peter,Gilbert”, “,”) – 1) | Peter | Extract substring from position 1 to (comma position-1) |
Mid(“Peter,Gilbert”, InStr(1, “Peter,Gilbert”, “,”) + 1, Len(“Peter,Gilbert”)) | Gilbert | Extract substring from (comma position+1) to (length of whole string) |
Outbound References
http://www.techonthenet.com/excel/formulas/mid.php