ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address