Excel MID Function
The Excel MID function returns the specified number of characters from a selected string, starting at a specified position
Example: Excel MID Function
|
=MID(B5,3,4)
|
Result in cell E5 (cd12) - returns the third, fourth, fifth and sixth character from the selected text string in cell (B5), given the formula indicates the start position as the third character and to return four characters, including the third.
|
|
=MID(B6,3,3)
|
Result in cell E6 (809) - returns the third, fourth and fifth character from the selected date in cell (B6), given the formula indicates the start position as the third character and to return three characters, including the third. Given cell (B6) contains a date, the formula will convert the date to a numeric date value, which is 42809.
|
METHOD 2. Excel MID Function using links
EXCEL
|
=MID(B5,C5,D5)
|
Result in cell E5 (cd12) - returns the third, fourth, fifth and sixth character from the selected text string in cell (B5), given the formula indicates the start position as the third character (value in cell (C5)) and to return four characters (value in cell (D5)), including the third.
|
|
=MID(B6,C6,D6)
|
Result in cell E6 (809) - returns the third, fourth and fifth character from the selected date in cell (B6), given the formula indicates the start position as the third character (value in cell (C6)) and to return three characters (value in cell (D6)), including the third. Given cell (B6) contains a date, the formula will convert the date to a numeric date value, which is 42809.
|
METHOD 3. Excel MID function using the Excel built-in function library with hardcoded values
EXCEL
Formulas tab > Function Library group > Text > MID > populate the input boxes
| =MID(B5,3,4) Note: in this example we are populating all the input boxes associated with the MID function which returns the third, fourth, fifth and sixth character from the selected text string in cell (B5). |
 |
METHOD 4. Excel MID function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Text > MID > populate the input boxes
| =MID(B5,C5,D5) Note: in this example we are populating all the input boxes associated with the MID function which returns the third, fourth, fifth and sixth character from the selected text string in cell (B5). |
 |
Sub Excel_MID_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("MID")
'apply the Excel MID function
ws.Range("E5") = Mid(ws.Range("B5"), 3, 4)
ws.Range("E6") = Mid(ws.Range("B6"), 3, 3)'Note: this will return /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style
ws.Range("E5") = Mid(ws.Range("B5"), 3, 4)
ws.Range("E6") = Mid(ws.Range("B6"), 3, 3)'Note: this will return /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named MID.
Worksheet Name: Have a worksheet named MID.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: The output in cell E6 will be /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style.
METHOD 2. Excel MID function using VBA with links
VBA
Sub Excel_MID_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("MID")
'apply the Excel MID function
ws.Range("E5") = Mid(ws.Range("B5"), ws.Range("C5"), ws.Range("D5"))
ws.Range("E6") = Mid(ws.Range("B6"), ws.Range("C6"), ws.Range("D6"))'Note: this will return /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style
ws.Range("E5") = Mid(ws.Range("B5"), ws.Range("C5"), ws.Range("D5"))
ws.Range("E6") = Mid(ws.Range("B6"), ws.Range("C6"), ws.Range("D6"))'Note: this will return /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named MID.
Worksheet Name: Have a worksheet named MID.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell references ("E5") and ("E6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: The output in cell E6 will be /03 given that the VBA code recognises cell B6 as a text string, not date. The VBA code only recognises a date in United States format and this example is written in English style.
DESCRIPTION
The Excel MID function returns the specified number of characters from a selected string, starting at a specified position.
The Excel MID function returns the specified number of characters from a selected string, starting at a specified position.
SYNTAX
=MID(text, start_num, num_chars)
=MID(text, start_num, num_chars)
ARGUMENTS
text: (Required) The string from which to extract the characters.
start_num: (Required) The position of the first character in the string that is to be extracted.
num_chars: (Required) The number of characters to extract from the specified string, starting at the specified position.
text: (Required) The string from which to extract the characters.
start_num: (Required) The position of the first character in the string that is to be extracted.
num_chars: (Required) The number of characters to extract from the specified string, starting at the specified position.
ADDITIONAL NOTES
Note 1: The MID function ignores the number formatting.
Note 2: In reference to date format the MID function recognises its numeric value.
Note 3: The MID function ignores decimal places.