Count number of substring occurrences

Question

I am a basic excel user, I am handling an excel work book in 2016 version, I’ve got a query as follows:

I have 9 worksheets in an excel book and a specific column in each sheet which displays a series of reference numbers as:

GAD5-CDC-T2-349-230315-DWG-PP-STR-0114-0
GAD5-MGM-T2-349-230315-DWG-PP-STR-0114-0
GAD5-CDC-T2-349-230315-DWG-SD-STR-0114-0
GAD5-MGM-T2-363-250315-DWG-PP-STR-0119-0
GAD5-CDC-T2-363-250315-DWG-DD-STR-0119-0
GAD5-CDC-T2-363-250315-DWG-BD-STR-0119-0
GAD5-CDC-T2-259-51-050515-DWG-FD-S-0233-00
GAD5-CDC-T2-259-51-050515-DWG-TD-S-0233-00
GAD5-MGM-T2-259-51-050515-DWG-LD-S-0233-00
GAD5-MGM-T3-119-25-DDS-ST-1568-02
GAD5-MGM-T3-119-25-DDS-RT-1568-02
GAD5-MGM-T3-119-25-DDS-OT-1568-02

and so on, Please note: these numbers are not following any sequence. I want to count the number of times the second last series of number is appearing. In this Instance

0114 is 3 times
0119 is 3 times
0233 is 3 times &
1568 is 3 times

I want to count this by a formula and get the result in any other cell as a counter. I’ll appreciate if you could help me in this please.

score 0 · Answer 1 · edited Mar 20 '17 at 10:17

Based upon the information you've provided, this VBa does it. I know you didn't ask for VBa but I wrote this in case you don't get another answer, and it may help others (and it works!)

You will need to format the column B as type Text (assuming that column B is where you want the results)

I have assumed that the data will be sequential as per your example.

Sub WalkThePlank()

Dim startRow As Integer
startRow = 1                        'Update this cap'ain as ye like

Dim column As String
column = "A"                        'The column for ye data

Dim resultsColumn As String
resultsColumn = "B"                 'This be the column you want the results!

Dim resultsColumnCount As String
resultsColumnCount = "C"                 'This be the column you want the results!


Do While (Range(column & startRow).Value <> "")

    Dim content As String
    content = Range(column & startRow).Value

    Dim splitty() As String
    splitty = Split(content, "-")

    Dim resultDigit As String ' must be string as your values start with a 0
    resultDigit = splitty(UBound(splitty) - 1)

    Dim resultsRow As Integer
    resultsRow = 1

    Do While (Range(resultsColumn & resultsRow).Value <> "")

        Dim resultsVal As String
        resultsVal = Range(resultsColumn & resultsRow).Value

        If resultsVal = resultDigit Then
            Range(resultsColumnCount & resultsRow).Value = Range(resultsColumnCount & resultsRow).Value + 1
        End If

        If resultsVal <> resultDigit And Range(resultsColumn & resultsRow + 1).Value = "" Then
            Range(resultsColumn & resultsRow + 1).Value = resultDigit
            Range(resultsColumnCount & resultsRow + 1).Value = 0
        End If


        resultsRow = resultsRow + 1
    Loop


    If (Range(resultsColumn & "1").Value = "") Then
        Range(resultsColumn & "1").Value = resultDigit
        Range(resultsColumnCount & "1").Value = 1
    End If

startRow = startRow + 1
Loop


End Sub

Before

After

The only difference in the data is I've not used 3 of each, I've used different values to show that it counts correctly.

How do I add VBA in MS Office?

Count number of substring occurrences

1 Answers1