Return to VBA Code Examples

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:

 

VBASplit Example

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)

 

The result of which would be:

VBASplit Left

 

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.

VBASplit Replace

 

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! vba save as


Learn More!