Delimited Text In Worksheet Cells
This page describes formulas for working with delimited text.
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
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.
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)))
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.
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
|
This page last updated: 25-March-2011. |