VBA Mid Function

What is Mid() function in VBA?

  • If you require a substring from a given string then 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:

VBA Mid

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:

VBA Mid no length

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 the text “Out of Stock” as shown below:

Code:

Sub mid_ex()
    MsgBox "Returned String after Mid =" & Mid(Range("B7"), 4)

End Sub

Output:

VBA Mid cell

Writing part of the B column’s text in the 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 Characters of each cell are 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:

VBA Mid cell range

Author - Atiq Zia

Atiq is the writer at toolsandtuts.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!