Excel String Parsing

Question

I have a spreadsheet that uses a string convention to convey levels of detail about individual "cases."

The convention is as follows: There are 5 main categories for the data in the string. 1=CATEGORY, 2=TOOLS, 3=DOCUMENTATION, 4=PROCESS, 5=JOB AID.

Each category can have multple sub categories broken down by 1a, 1b, 1c or 2a, 2b, 2c, etc for each 5 category.

Main category is separated from sub category by ":" and sub catory is separated from sub cateogry by "," whereas sub catory is separated from a new Main category by a ";"

an example of a string: "1:1i;2:2a;3:3a,3d,3l;4:4a"

Here is a sample breakdown of category in values:

CATEGORY    1
Incorrect:VG:QOC    1i

TOOLS   2
Macro:Used  2a

DOCUMENTATION   3
TAT:Missing 3a
ROUTING:Missing 3d
STORY:Missing Impact to Health  3i

PROCESS 4
CNX Checklist Not Used  4a

I want to be able to pull out in text form what main categories and sub categories were flagged.

Example query:
Of all strings, how many times was main category 4 flagged? Of all 4 flagged, how many times was 4a flagged? What were all elements flagged on one "case" or string?

I can build the strings, I just cannot parse them. Please help... :)

Answer 1

The function you are looking for is Split (this link is for the VB function, but the behavior is virtually the same in VBA). You can pass a specific string to it and specify the delimiter, and it will return an array of each value.

In your case, since your string has multiple delimiters, you'll need to do it multiple times.

First potential issue is what if you do not have a subcategory for a given category? If you will always have at least subcategory for each category in your string, then that's fine, but if there is potentially a case where there is no subcategory, then you need to make sure your highest level grouping is still delimited by a ;.

Since you didn't say how the information is going to get presented to the user, the below example will print something close to what it sounds like you are expecting in the intermediate window in Excel.

Option Explicit

Sub SplitExample()
Dim inputString As String
Dim categories() As String
Dim subCategories() As String
Dim individualSubCat() As String
Dim cat As Variant
Dim subCat As Variant
Dim cnt As Integer

    inputString = "1:1i;2:2a;3:3a,3d,3l;4:4a"

    categories = Split(inputString, ";")

    For Each cat In categories

        subCategories = Split(cat, ":")

        If UBound(subCategories) >= 0 Then
            Debug.Print ("Category " & subCategories(0))

            If UBound(subCategories) > 0 Then
                individualSubCat = Split(subCategories(1), ",")
                Debug.Print (vbTab & "Has " & UBound(individualSubCat) - LBound(individualSubCat) + 1 & " flag(s)")

                For Each subCat In individualSubCat
                    Debug.Print (vbTab & subCat & " was flagged " & CountSubCategory(individualSubCat, subCat) & " time(s)")
                Next

            Else
                Debug.Print (vbTab & "No Subcategories flagged")
            End If

            Debug.Print ("")

        End If

        Erase subCategories
        Erase individualSubCat

    Next

End Sub

This is a function that will count the subcategories easily

Private Function CountSubCategory(individualSubCategories() As String, ByVal subCat As String) As Integer
Dim cnt As Integer
Dim value As Variant

    cnt = 0

    For Each value In individualSubCategories
        If value = subCat Then cnt = cnt + 1
    Next

    CountSubCategory = cnt

End Function

And using your example string as an input, the above code will print:

Category 1
    Has 1 flag(s)
    1i was flagged 1 time(s)

Category 2
    Has 1 flag(s)
    2a was flagged 1 time(s)

Category 3
    Has 3 flag(s)
    3a was flagged 1 time(s)
    3d was flagged 1 time(s)
    3l was flagged 1 time(s)

Category 4
    Has 1 flag(s)
    4a was flagged 1 time(s)

The above code will print every flag, even if there are duplicates. You didn't say whether or not that was desired behavior. Filtering or grouping duplicates from an array is not simple, but is best done with the Collection or Dictionary class in VBA. (Take a look at this question for help filtering duplicates from an array).

The above code is just an example to show what needs done and how to accomplish the parsing (as that was your specific request). To actually make this into working code, you would need to do 2 things:

1. Create a Function or Sub in VBA with the code to parse it above (basically what is inside SplitExample() above) and give it a name (like ParseErrorCodes), and have it accept a string parameter called inputString. You would then call it from the method that builds the string (which you said you already can do) and pass that string to the method.
2. Decide how to output the results. Effectively you would replace the Debug.Print lines with something that will output the results somewhere (presumably to another excel spreadsheet so you can create the chart that you were looking for).

The basic idea would be:

Sub OutputErrorCodes()
Dim inputString as String

    ' You code to read your string values from where-ever you keep them
    '  and build the inputString
    ' this source could be a file, or a worksheet in the Excel Workbook
    '  or could be an external datasource like a database or even an internet 
    '  location
    ' once you build your inputString, you just need to call ParseErrorCodes 

    ParseErrorCodes inputString

End Sub

Sub ParseErrorCodes(input as String)

    ' MyCode from above with some modifications
    '  - You need to remove the Dim for inputString and the assignment for 
    '     inputString
    '  - You need to replace the Debug.Print lines with your method of 
    '     defining the output
    '    *  this can be via outputing directly to an excel spreadsheet or 
    '        maybe a global variable
    '    *  outputing to an excel spreadsheet would probably be the best 
    '        option and allow for more dynamic flags, but you need to decide 
    '        where to output it in the code

End Sub

Private Function CountSubCategory(individualSubCategories() As String, 
                                  ByVal subCat As String) As Integer)

    ' this code can probably be taken directly from my example

End Function