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