Split a string and return any individual part
2005-08-06 Strings 0 260
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)