Split a string and return any individual part

This function splits a character separated string into its indivual parts and returns any given part.
The function can also be used as a worksheet function.
The function will only work in Excel 2000 or later.

Function GetStringPart(strInput As String, strDelimiter As String, _
    intPart As Integer) As String
Dim varStrings As Variant
    varStrings = Split(strInput, strDelimiter, -1, vbBinaryCompare)
    On Error Resume Next
    GetStringPart = Trim(varStrings(intPart - 1))
    On Error GoTo 0
End Function

Example:
If cell A1 contains this string:
Doe, John, Streetname 15, 12345 Town, Statename

=GetStringPart(A1,",",1) will return "Doe"
=GetStringPart(A1,",",4) will return "12345 Town"
=GetStringPart(A1,",",9) will return "" (a blank/empty string)

In VBA the function can be used like this:
strFirstName = GetStringPart(Range("A1").Value, ",", 2)

Document last updated 2005-08-06 14:06:04 Printerfriendly version