# piece function

• ### 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