Excel/VBA 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: