VBA Parse String

This article will demonstrate how to parse a string in VBA.

Often we will use Excel to import data from other files. Occasionally this data might be in a format that is not very user friendly, or that we need to amend to bring the information into Excel in a logical way. There are a number of string functions we can use in Excel VBA to extract the data correctly from the string that is brought in.

VBA Split Function

If the string comes into Excel as a delimited string (ie separated by commas, semi-colons, etc.), we can easily split the string into the individual values by using the Split function.

For example, say we have this string of names:

“John, Mary, Jack, Fred, Melanie, Steven, Paul, Robert”

Using the split function, we can return these names to Excel individually:

Sub SplitText()
 Dim strT As String
 Dim strArray() As String
 Dim name As Variant

'populate the string with names
 strT = "John,Mary,Jack,Fred,Melanie,Steven,Paul,Robert"

'populate the array and indicate the delmiter
 strArray = Split(strT, ",")

'loop through each name and display in immediate window
 For Each name In strArray
   Debug.Print name
 Next
End Sub

 

VBA Left, Right and Mid Functions

We can also extract data from strings by using the Left, Right and Mid functions.  They are not as efficient as using the Split function to get multiple values from a string, but if you need to separate a line into specific areas, they can be useful.

For example, say our file name is “C:\Data\TestFile.xls” .  Now this includes the drive, the folder on the drive, the name of the file and the file extension.

To get the drive that the file is stored on we can use:

LEFT(“C:\Data\TestFile.xls”, 1) – which will return C.

To get the Path including the drive we can use:

LEFT(“C:\Data\TestFile.xls”, 7) – which will return C:\Data.

To get the name of the file only, we can use MID:

MID(“C:\Data\TestFile.xls”, 9,8) – which will return TestFile

To get the extension of the file we can use:

RIGHT(“C:\Data\TestFile.xls”, 3)

 

Sub ExtractData()
 Dim strData As String
 Dim strLeft As String
 Dim strRight As String
 Dim strMid As String

'populate the string
 strData = "C:\Data\TestFile.xls"

'break down the name
 strLeft = Left(strData, 7)
 strMid = Mid(strData, 9, 8)
 strRight = Right(strData, 3)

'return the results
 MsgBox "The path is " & strLeft & ", the File name is " & strMid & " and the extension is " & strRight
End Sub

The result of which would be:

 

VBA Replace Function

Another useful string function to manipulate strings in Excel, is the Replace function. This can be used to remove anything from a string and replace it with something else. This is particularly useful if the string that you have brought into Excel has characters that your coding will not recognize, or will mess up your data.

For example:

Consider the following string:

“John””Mary””Jack””Fred””Melanie””Steven””Paul””Robert”””

We can replace the double-quotes with commas using the Replace function.

Sub ExtractData()
  Dim StrData As String
  StrData = "John""Mary""Jack""Fred""Melanie""Steven""Paul""Robert"""
  StrData = Replace(StrData, """", ",")
  MsgBox StrData
End Sub