Access Mid Function to extract substring

This Access tutorial explains how to use Access MID Function to extract substring from a string.

Access 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. Other than Access, Mid Function can also be used in Excel worksheet, Excel / Access VBA.

Access MID Function is commonly used with Instr Function (see below example), while Excel MID Function can be used with Search Function and Find Function.

Syntax of Access 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 Access 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

https://support.office.com/en-us/article/Mid-Function-427E6895-822C-44EE-B34A-564A28F2532C

 

Leave a Reply

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