piece function RRS feed

  • Question

  • I need a function that will extract a piece from a string, given a specified delimiter, i.e. strPiece3=Piece(strString,"|",3)

    For instance, given the string "abc|def|ghi|jkl"  extract piece 3 using "|" as a delimiter, returns "ghi"

    If the string were abcdefghi (no delimiter found) and you ask for piece 1, return the entire string (assume a delimiter is at end).

    If the string did not contain the delimiter, and you asked for piece 2, return null.

    If the you asked for piece 5 in the above string, return null.

    I can't imagine that this handy function has not been written, it should be in the language function set. 

    Maybe it is, I am fairly new at vba language.

    Sunday, January 22, 2012 5:06 PM


  • Like this?


    Option Explicit
    Sub Sample()
        Dim strString As String
        '~~> TEST CASE 1
        strString = "abc|def|ghi|jkl"
        Debug.Print piece(strString, "|", 3)
        '~~> TEST CASE 2
        strString = "abcdefghi"
        Debug.Print piece(strString, "|", 1)
        '~~> TEST CASE 3
        strString = "abcdefghi"
        Debug.Print piece(strString, "|", 2)
    End Sub
    Function piece(strInput As String, Delim As String, nPart As Long) As String
        If Len(Trim(strInput)) = 0 Then piece = "NULL": Exit Function
        If Len(Trim(Delim)) = 0 Then piece = "NULL": Exit Function
        piece = strInput
        If InStr(1, strInput, Delim, vbTextCompare) Then
            Dim MyArray() As String
            MyArray = Split(strInput, Delim)
            If nPart > (UBound(MyArray) + 1) Then piece = "NULL": Exit Function
            piece = MyArray(nPart - 1)
        ElseIf nPart > 1 Then
            piece = "NULL"
        End If
    End Function


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Sunday, January 22, 2012 9:30 PM