4.7 The Mid Function in Excel VBA

The final String function we'll look at is Mid. This function is used to grab characters from a string of text. It has three parts:

Mid(string_to_search, start_position, number_of_characters_to_grab)

The first part is the string you want search. This can be a variable or direct text between double quotes. The second part is where in the string you want to start grabbing characters from. The final part is how many characters you want to grab.

To demonstrate the Mid function, examine the following code:

Dim Email As String
Dim GrabbedChars As String

Email = "myaddress@myisp.com"

GrabbedChars = Mid(Email, 16, 4)

MsgBox GrabbedChars

We've set up two String variables here, one called Email and one called GrabbedChars. We've stored an email address in the Email variable. Then comes our Mid code:

GrabbedChars = Mid(Email, 16, 4)

The text we're searching is in the Email variable. We want to start grabbing characters from position 16 in the string. The numbers of characters we want to grab is 4.

When the programme is run, the message box will display .com.

The Mid function is very useful in loops, as it allows you to examine one character at a time from a string of text.

In the next lesson, we'll present you with a probem that will test your new String Method skills.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney