VBA Mid Function – Extract Characters From Middle of String

This tutorial will demonstrate how to use the Mid VBA function to extract characters from the middle of a text string.

Mid Function

Mid Function Get n Characters

The VBA Mid function returns n characters from a string starting from position m:

Sub MidExample_1()
MsgBox Mid("ABCDEFGHI", 4, 1)   'Result is: "D"
MsgBox Mid("ABCDEFGHI", 4, 2)   'Result is: "DE"
MsgBox Mid("ABCDEFGHI", 4, 50) 	'Result is: "DEFGHI"

MsgBox Mid("ABCDEFG hI", 6, 1)  'Result is: "F"
MsgBox Mid("ABCDEFG hI", 6, 2)  'Result is: "FG"
MsgBox Mid("ABCDEFG hI", 6, 4) 	'Result is: "FG h"
End Sub

Mid Function Get n Characters in a Variable

As shown above, you can define a string simply by entering text surrounded by quotation marks. But the MID Function will also work with string variables. These examples will extract n characters from a string starting from position m.

Sub MidExample_2()
Dim StrEx As String 'Define a string variable

StrEx = "ABCDEFGHI"
MsgBox Mid(StrEx, 2, 1)		'Result is: "B"
MsgBox Mid(StrEx, 2, 2)       	'Result is: "BC"
MsgBox Mid(StrEx, 2, 50) 	'Result is: "BCDEFGHI"
End Sub

Mid Function Get n Characters from a Cell

Strings can be defined in VBA code but also you can use values from cells. Read the value of a cell, keep it in a string variable, and extract n characters from that Worksheet Cell value starting from position m.

Sub MidExample_3()
Dim StrEx As String 'Define a string variable

'Read the value of cell A1 in worksheet Sheet1
StrEx = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
'For this example the value of cell A1 is "May the Force be with you"

MsgBox Mid(StrEx, 4, 6) 'Result is: " the F" (Note the space at the start)
MsgBox Mid(StrEx, 2, 8) 'Result is: "ay the F"
MsgBox Mid(StrEx, 3, 4) 'Result is: "y th"
End Sub

Mid Function Replace n Characters

In the examples above, Mid function did not change the original string. It returned a part of it, leaving the original string intact. Mid Function can be used to replace characters in a string.

Sub MidExample_4()
Dim StrEx As String 'Define a string variable

Sub MidExample_4()
Dim StrEx As String 'Define a string variable

StrEx = "May the Force be with you"
Mid(StrEx, 5, 1) = "VWXYZ"
MsgBox StrEx 'Result is: "May Vhe Horce be with you"
'Mid Function found position 5 and replaced 1 character in the original string

StrEx = "May the Force be with you"
Mid(StrEx, 5, 3) = "VWXYZ"
MsgBox StrEx 'Result is: "May VWX Horce be with you"
'Mid Function found position 5 and replaced 3 characters in the original string

StrEx = "May the Force be with you"
Mid(StrEx, 5, 8) = "VWXYZ"
MsgBox StrEx 'Result is: "May VWXYZorce be with you"
'Mid Function found position 5 and tried to replaced 8 characters. 
'"VWXYZ" has only 5 characters, so only 5 characters were replaced.
End Sub

Mid Function Extract Second Word from a Phrase

We can use VBA Mid function with VBA Instr function to get the second word in a text.

VBA InStr function can return the position of a character inside the text.

InStr("Two words", " ") 'Result is 4

We can use InStr to find the first space, then we can use again InStr starting the search after the first space to find the second space in the text. Finally, we can use Mid function to extract the word because we know the starting position of the second word and its length (the difference between the two spaces positions).

Sub MidExample_5()
Dim StrEx As String 'Define a string variable
Dim StartPos As Integer
Dim EndPos As Integer
Dim SecondWord As String
StrEx = "James Earl Jones is an Actor"

StartPos = InStr(StrEx, " ")
'Result is 6
'Find the position of the first space

EndPos = InStr(StartPos + 1, StrEx, " ")
'Result is 11
'Find the position of the second space by starting search after the first space


SecondWord = Mid(StrEx, StartPos + 1, EndPos - StartPos - 1)
'Mid extracts the characters starting after the first space (StartPos +1)
'Mid uses also the length of the second word.
'That is the difference between spaces positions -1

MsgBox SecondWord
'Result is Earl

End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!