Excel/VBA Mid function
If you require a substring from a given string, use the Mid function. You may specify the number of characters to start form in the given string in the Mid function.
What is Mid() function in VBA?
- If you require a substring from a given string, use the Mid function.
- You may specify the number of characters to start from in the given string in the Mid function.
- Similarly, length – till which character the substring can be specified. This is an optional argument and if you omit this, the rest of the string is returned.
Syntax:
Mid(string, start, [ length ])
An example of Mid() with all arguments
- We declared a string variable and assigned it a value.
- In the Mid() function, we specified that string and also start and length arguments are given.
- The returned substring is displayed in the message box
Code:
Sub mid_ex() Dim str_mid As String str_mid = "This is VBA tutorial!" MsgBox "Returned String after Mid =" & Mid(str_mid, 8, 13) End Sub
Result:
Related: You may also want to learn about using the MID function in Excel directly.
What if we omit the length argument example
We only provided two arguments this time: String variable and start. See the code and output:
Sub mid_ex() Dim str_mid As String str_mid = "This is VBA tutorial!" MsgBox "Returned String after Mid =" & Mid(str_mid, 8) End Sub
Output:
You saw it returned “!”, as well.
Taking Excel cell text and using Mid function example
This time, we took the B7 cell text and used it in the Mid() function. The B7 cell contains text “Out of Stock” as shown below:
Code:
Sub mid_ex() MsgBox "Returned String after Mid =" & Mid(Range("B7"), 4) End Sub
Output:
Writing part of the B column’s text in C column example
In this example, we created a range of cells (B2:B10).
In the Mid() function, we passed the range cells one by one by using the For..Each loop.
From 3 to 7 Character of each cell is written to the C column’s respective cell.
Code:
Sub mid_ex() Dim rng_dim As Range Dim x x = 2 Set rng_dim = Range("B2:B10") For Each cell In rng_dim Range("C" & x) = Mid(cell, 4) x = x + 1 Next End Sub
Output: