find how many times a word appears in a string at certain positions within the string

they indicate the position of the word in the string. the ~ characters a merely an identifyer.

the position starts at 0. The positions 0 to 5 are always filled with data which is to be ignored. so relating this back to the example i posted above..

myString = "14~ThisWorkbook.Name~Sheets(1).Name~E~11~25~data0~data1~data2~data3~data4~data5~data6~data7~data8~data9~data10~data11~data12~data13"

0 = 14, 1 = ThisWorkbook.Name, 2 = Sheets(1).Name, 3 = E, 4 = 11, 5 = 25.
From 6 onwards it is all 'cell block' data - the coloured square ranges on the workbook that always consist of 1 column and 7 rows.
I only wish to search on the first row of these 'cell blocks' for the term. therefore only positions 6, 6+7, 6+7+7, 6+7+7+7, ... etc should be checked to see if they contain the key word.

I hope this explanation helped and didnt hinder! haha

Thanks again
jordan

Yes everything before the 6th is to be ignored, then only the words immediately left of the 7th, 14th, 21st tildes should be checked if theyre value is like the term. then return the number of instances this term appears in checked

For what its worth here is a snippet of the code I am using to compare the values of the string. The strings are stored in an array.
The code looks for the term in the string, and if it finds the term within the string, it changes the colour of the cells that the string relates to.
The string contains the workbook, sheet, column, & row information within it, using this information and the split function I am able to make the colour changes to the cells that belong to the String.

    For i = 2 To UBound(Reference_Array, 1)
        For j = 0 To UBound(Reference_Array, 2)
            
            For term_counter = 1 To 10
            
                Select Case term_counter
                    'for this loop assign this as the term to find
                    Case 1
                        term = "RDO": cell_block_colour = 38
                    'for this loop assign this as the term to find
                    Case 2
                        term = "ANNUAL LEAVE": cell_block_colour = 5
                    'for this loop assign this as the term to find
                    Case 3
                        term = "PUBLIC HOLIDAY": cell_block_colour = 23
                    'for this loop assign this as the term to find
                    Case 4
                        term = "STAFF TRAINING": cell_block_colour = 50
                    'for this loop assign this as the term to find
                    Case 5
                        term = "VEHICLE INSPECT": cell_block_colour = 49
                    'for this loop assign this as the term to find
                    Case 6
                        term = "FSNe": cell_block_colour = 43
                    'for this loop assign this as the term to find
                    Case 7
                        term = "MBI Project": cell_block_colour = 11
                    'for this loop assign this as the term to find
                    Case 8
                        term = "CCW": cell_block_colour = 51
                    'for this loop assign this as the term to find
                    Case 9
                        term = "PPW": cell_block_colour = 51
                    'for this loop assign this as the term to find
                    Case 10
                        term = "MUP": cell_block_colour = 51
                
                End Select
                
                If Reference_Array(i, j) Like "*" & term & "*" Then
                    
                    'set the split array
                    sp = Split(Reference_Array(i, j), "~")
                    
                    'set the array string
                    array_string = Reference_Array(i, j)
                    
                    'Find the number of rows from the top row of all the cell blocks to the found cell block
                    cell_block_rows_from_top = InStr(1, array_string, term, 1) - InStr(1, Replace(array_string, "~", ""), term, 1) - 6

                    'Only do this for new cell blocks that start this far from the top row
                    If cell_block_rows_from_top = 0 Or cell_block_rows_from_top = 7 Or cell_block_rows_from_top = 14 Then
                        
                        'set the total number of rows in the data
                        total_number_of_rows = sp(0)
                        
                        'set the cell block top row
                        top_row = sp(4) + cell_block_rows_from_top
                        
                        'set the cell block bottom row
                        bottom_row = top_row + 6
                        
                        'set the cell block column
                        column = sp(3)
                        
                        'change the colour of the cell block
                        Workbooks(sp(1)).Sheets(sp(2)).Range(column & top_row & ":" & column & bottom_row).Interior.ColorIndex = cell_block_colour
                    End If
                    
                End If
            Next term_counter
        Next j
    Next i