Excel Developer Tip

Excel page

Tip archives

Extracting the n-th Element From a String

Excel has many worksheet functions, but sometimes you'll find that it just doesn't have the function you need. In such a situation, it's time to roll your own.

This tip describes a custom worksheet function (which can also be called from a VBA procedure) that extracts an element from a text string. For example, if a cell contains the text below, you can use the ExtractElement function to extract any of the substrings between the hyphens.

  123-456-789-0133-8844

The formula below, for example, returns "0133", which is the fourth element in the string. The string uses a hyphen (-) as the separator.

  =ExtractElement("123-456-789-0133-8844",4,"-")

NOTE:
If you use Excel 2000 or later, click here for a much simpler approach.

ExtractElement Syntax

The ExtractElement function uses three arguments:

• Txt: The text string from which you're extracting.  Txt can be a literal string or a cell reference.
• n: An integer that represents the element to extract.
• Separator: A single character that is used as the separator.

Usage Notes

• If you specify a space as the Separator character, multiple spaces are treated as a single space -- which is almost always what you want.
• If n exceeds the number of elements in the string, the function returns an empty string.

The ExtractElement Function

The VBA code for the ExtractElement function is listed below. To use this function, copy the text and paste it into a VBA module.

Function ExtractElement(Txt, n, Separator) As String
'   Returns the nth element of a text string, where the elements
'   are separated by a specified separator character

    Dim Txt1 As String, TempElement As String
    Dim ElementCount As Integer, i As Integer
    
    Txt1 = Txt
'   If space separator, remove excess spaces
    If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
    
'   Add a separator to the end of the string (if necessary)
    If Right(Txt1, 1) <> Separator Then Txt1 = Txt1 & Separator
    
'   Initialize
    ElementCount = 0
    TempElement = ""
    
'   Extract each element
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            ElementCount = ElementCount + 1
            If ElementCount = n Then
'               Found it, so exit
                ExtractElement = TempElement
                Exit Function
            Else
                TempElement = ""
            End If
        Else
            TempElement = TempElement & Mid(Txt1, i, 1)
        End If
    Next i
    ExtractElement=""	
End Function