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:
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.
Consider the following string:
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
VBA Coding Made EasyStop 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!