VBA — String Parsing

String parsing involves looking through each character in a string. Typically it is best to split the string on spaces or another indicator such as “.” or “/” so only individual words are being observed. This process can used to manipulate words in a string or identify parts of strings.

For Example:

Dim checkString as String
Dim stringArray() as String
Dim tCount as Integer
Dim tWordArray() as String

checkString = "Trying to identify words that have more than one t: like matter or scatter"

stringArray = Split(checkString, " ")

For each word in checkString

   tCount = 0

   For i = 1 to len(word)

      if mid(word, i, 1) = "t" Then tCount = tCount + 1

   Next i

   if tCount > 1 Then

      if len(join(tWordArray)) = 0 Then

         Redim preserve tWordArray(0)

      else

         Redim preserve tWordArray(Ubound(tWordArray) + 1)

      End if 

      tWordArray(Ubound(tWordArray)) = word

   End if

Next word

The code above splits the checkString on each space. The Split method will automatically split the string on spaces if not specified. It can split on any indicator whether is be commas, dashes, you get the point. The Split method returns an array of strings.

We loop through the array of words. Then we loop through each character in the word by using the mid method which takes the a segment of a string. We specify that we want to take one character from the word string at character i. Note that the mid method takes the character number and not the index, therefore we run from 1 to the length of the word. If the character at i is t we increment the tCount variable.

If the tCount is greater than 1 we add the word to the tWordArray. We first check if there is anything in the array by checking the length of all items in the array. If the length is 0 then we know there are no items. We then can set the dimension to be 0. If the length is not 0 then there is already an item in the array and we call the Ubound method to find the number of indices in the array.

We end with having an array of strings. The strings should be “matter” and “scatter”. This example explores how to identify parts of a string. We can also manipulate the string. For example we could have changed each t to an a. There are many possibilities with string manipulation and this is a quick example to show the power of strings.