ThreeWave Delimited Text In Worksheet Cells

This page describes formulas for working with delimited text.
ShortFadeBar

Introduction

Often, data is stored in a single worksheet cell consisting of several values, separated by some delimiter. For example, a cell may contain Abby,Barb,Carrie,Dawn, a list of names separated by commas. Suppose this text is in cell A1. You can count the number of delimiters with the following formula, where the delimiter character is in cell D2: 

    =LEN(A1)-LEN(SUBSTITUTE(A1,D2,""))

With the example data above, this will return the value 3, indicating that there are 3 commas in the string in A1. The number of elements is simply the number of delimiters plus 1, or: 

    =LEN(A1)-LEN(SUBSTITUTE(A1,D2,""))+1

Excel 2007 And Later

You can get a specific element in the text string with the following array formula. It assumes that the text is in cell A1, the delimiter character is in cell D2, and the ordinal number (between 1 and the number of string elements) is in cell D3. 

=IF(OR(LEN(D2)<>1,D3<=0,D3>LEN(A1)-LEN(SUBSTITUTE(A1,D2,""))+1,
LEN(A1)=0,ISERROR(FIND(D2,A1))),NA(),IF(D3=1,LEFT(A1,FIND(D2,A1)-1),
MID(A1,SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1)+1,D3-1),
SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1),D3)-
SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1)+1,D3-1))))

Due to the depth of nested functions, this formula will work only in Excel 2007 and later. It will not work in Excel 2003 and earlier. See the next formula for a solution that will work in Excel 2003 and earlier.

NOTE: This is a array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You do not type in the braces -- Excel puts them in automatically. The formula will not work correctly if you do not use CTRL SHIFT ENTER to enter the formula. See the array formulas page for much more information about array formulas.

For example, suppose A1 contains Abby,Barb,Carrie,Dawn, cell D2 contains the comma character, and cell D3 contains the value 3, indicating that the third element is to be extracted. The result of the formula is Carrie, the third element in A1. The formula will return a #N/A error if any one or more of the following conditions are true:

  • The delimiter (D2) is not exactly one character.
  • The delimiter (D2) is not found in the text string (A1).
  • The ordinal (D3) is greater than the number of elements in the text string (A1).
  • The ordinal (D3) is less than or equal to zero.
  • The text string (A1) is empty.

Excel 97 And Later

A shorter version of the formula above without any error checking is shown below. This formula will work in all versions of Excel. Like the formula above, it is an array formula. 

=IF(D3=1,LEFT(A1,FIND(D2,A1)-1),
MID(A1,SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1)+1,D3-1),
SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1),D3)-
SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1)+1,D3-1)))

Special Cases

There are two special cases for which we can use much simpler formulas. The first case is to extract the first text element. Use the following formula: 

=LEFT(A1,FIND(D2,A1)-1)

The second special case is to extract the last text element. Use the following array formula. 

=MID(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=D2,
ROW(INDIRECT("1:"&LEN(A1)))))+1,LEN(A1))

These formulas do not have any error checking; it is assumed that the values in the cells are valid.

VBA Implementation

You can get the same functionality with a VBA function. In the following function, the parameter S is the string to search, Delim is the delimiter character, and Ordinal indicates which text element to return. 

Function GetElement(S As String, Delim As String, _
        Ordinal As Long) As Variant
    Dim SS() As String
    If Len(S) = 0 Then
        GetElement = CVErr(xlErrNA)
        Exit Function
    End If
    If Len(Delim) <> 1 Then
        GetElement = CVErr(xlErrNA)
        Exit Function
    End If
    If InStr(1, S, Delim) = 0 Then
        GetElement = CVErr(xlErrNA)
        Exit Function
    End If
    If Ordinal <= 0 Then
        GetElement = CVErr(xlErrNA)
        Exit Function
    End If
    If Ordinal > Len(S) - Len(Replace(S, Delim, vbNullString)) + 1 Then
        GetElement = CVErr(xlErrNA)
        Exit Function
    End If
    SS = Split(S, Delim)
    GetElement = SS(Ordinal - 1)
End Function
ShortFadeBar
LastUpdate This page last updated: 25-March-2011.

-->