Split a string and return any individual part

 2005-08-06    Strings    0    82

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)


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.