Extracting a specified block of characters from a delimited string

[raskew]

In the process of responding to a post in another forum, I came across this function, which I hadn't used in years.
It allows the user to specify a delimiter and designated block (e.g. 1,2,3) and returns that block.
I don't know if I wrote it (would like to think so) or copied it from an unknown source. A search failed to turn
up the function name. If it's yours, please let us know.

Public Function PassbackAnyword(pstrText As String, pintword As Integer, pstrdivider As String) As Variant
'Purpose:   Given a string, a word and the divider,
'           returns the specific word.
'Input:     ? PassbackAnyword("1234*7890*8888", 2, "*")
'Output:    7890

Dim intLoop As Integer
Dim intPos As Integer
Dim intprev As Integer
Dim varstring As Variant
    
    'Don't waste your time if the divider isn't in the string
    If InStr(pstrText, pstrdivider) <> 0 Then
            
        intPos = 1
        intprev = 1
        
        pstrText = pstrText & pstrdivider
        For intLoop = 1 To pintword
        
            intPos = InStr(intprev + 1, pstrText, pstrdivider)
            
            If intPos <> 0 Then
                If intLoop < pintword Then
                    intprev = intPos
                End If
            Else
                intPos = intprev
            
            End If
        
        Next
        
        varstring = Mid(pstrText, intprev, intPos - intprev)

        If pintword > 1 And varstring <> "" Then
            varstring = Right(varstring, Len(varstring) - 1)
        End If
    Else
        'If it's the first word we want then it's all the string otherwise is nothing
        If pintword = 1 Then
            varstring = pstrText
        End If
    End If
    If varstring = "" Or varstring = pstrdivider Then
        varstring = Null
    End If
    
    PassbackAnyword = varstring
End Function

I've submitted it to the Code Repository, but I've included it here to provide a little more exposure because I think it may be useful in many situations, and it eliminates a bunch of gyrations trying to get just that specific block of characters.

HTH - Bob

 

[jonesey]

That has provided a great method for splitting out some legacy data I have been trying to work with into correctly normalised fileds.
Thank you

 

[DALeffler]

This code was inspired by the above posts and functionally differs from the code in the above posts for when the string delimiter is more than 1 character in length. I think the logic is a little easier to track but I'd bet that's just me. Hope it helps and doesn't confuse...

Public Function fncGetStringSec(strRcved As String, iPartNum As Integer, strDlmt As String) As String

fncGetStringSec = ""

'Check for non starters...
If Len(strRcved) < 1 Or iPartNum < 1 Or Len(strDlmt) < 1 Then Exit Function
If InStr(1, strRcved, strDlmt) < 1 Then Exit Function

'Exit if requested partnum is greater than num of parts in received string
If ((Len(strRcved) - Len(Replace(strRcved, strDlmt, ""))) / Len(strDlmt)) + 1 _
    < iPartNum Then Exit Function

'setup passed string for loop
strRcved = strRcved & strDlmt 

'strip chars before requested string section
While iPartNum > 1
    iPartNum = iPartNum - 1
    strRcved = Mid(strRcved, InStr(1, strRcved, strDlmt) + Len(strDlmt))
Wend

fncGetStringSec = Mid(strRcved, 1, InStr(1, strRcved, strDlmt) - 1)

End Function

 

[MarkK]

VBA.Strings.Split() Function

Here's one...

[SIZE="1"][FONT="Verdana"]Function GetDelimitedSubstring(Text As String, Index As Integer, Optional Delimiter As String = " ") As String
[COLOR="Green"]   'Uses the Split() function to return a variant array of elements, 
   'and returns the array element specified by Index
[/COLOR]   GetDelimitedSubstring = Split(Text, Delimiter)(Index - 1)
End Function[/FONT][/SIZE]