Find "-****-" within a string, where each star represents an integer (0 to 9) in VBA

Question

my brain is struggling a bit right now and I'm trying to accomplish what is said in the title. To reiterate, if I have an arbitrary string and I want to find the position of four consecutive numbers (of integer value between 0 and 9), separated by two "-", so I need to find "-****-" where each "*" represents an integer between 0 and 9. The ideal return value of the function would be the position of the first "-".

What I have tried so far, which returns all integers within an arbitrary string. I haven't been able to get much farther than this.

Function onlyDigits(s As String) As Integer
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '
    Dim firstDashPosition As Integer
    Dim foundDash As Boolean
    foundDash = False
    firstDashPosition = 0

    Debug.Print s
    retval = ""

    For i = 1 To Len(s)
        If Mid(s, i, 1) Like "-" Then
            If foundDash = True Then
                retval = retval + "-"
                firstDashPosition = i
                onlyDigits = firstDashPosition - 6
                Exit Function
            End If

            Debug.Print "here"
            foundDash = True
            retval = retval + "-"
        ElseIf Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" And foundDash = True Then
            retval = retval + Mid(s, i, 1)
        Else
            foundDash = False
            retval = ""
        End If
    Next
    onlyDigits = firstDashPosition
End Function

[This question and answer](http://stackoverflow.com/q/22542834/4002530) about regular expressions in VBA may be a good starting point — tospig, Apr 01 '15 at 00:15

score 1 · Accepted Answer · answered Apr 01 '15 at 00:31

Regular expressions might be a better solution but a simple one time solution would be

Function onlyDigits(s As String) As Integer

    Dim i As Integer
    For i = 1 To Len(s)

        If Mid$(s, 1, 6) Like "-[0-9][0-9][0-9][0-9]-" Then
           onlyDigits = i
           Exit Function
        EndIf
    Next

    onlyDigits = -1

End Function

score 0 · Answer 2 · answered Apr 01 '15 at 00:19

I figured it out, I think I was just tired after a long day. Here is the code if anybody is interested, and I edited it in the question

Function onlyDigits(s As String) As Integer
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '
    Dim firstDashPosition As Integer
    Dim foundDash As Boolean
    foundDash = False
    firstDashPosition = 0

    Debug.Print s
    retval = ""

    For i = 1 To Len(s)
        If Mid(s, i, 1) Like "-" Then
            If foundDash = True Then
                retval = retval + "-"
                firstDashPosition = i
                onlyDigits = firstDashPosition - 6
                Exit Function
            End If

            Debug.Print "here"
            foundDash = True
            retval = retval + "-"
        ElseIf Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" And foundDash = True Then
            retval = retval + Mid(s, i, 1)
        Else
            foundDash = False
            retval = ""
        End If
    Next
    onlyDigits = firstDashPosition
End Function

Find "-****-" within a string, where each star represents an integer (0 to 9) in VBA

2 Answers2