ELEMENT()

The ELEMENT function is used to extract individual values from a cell containing a list.

Cell A1: Dog,Cat,Mouse

=ELEMENT(a1,1)  returns "Dog"
=ELEMENT(a1,2)  returns "Cat"
=ELEMENT(a1,3)  returns "Mouse"
=ELEMENT(a1,4)  returns ""

=ELEMENT(a1,-1)  returns "Mouse"

While the ELEMENT() function can be implmented using the builtin Excel functions FIND, MID and other text functions, the ELEMENT function is easier to work with and handles very long lists better.

Syntax

ELEMENT(String-List,Index,Delimiter,Value if Blank,Options)

String-List

A list of values that have a delimiter between each value.

Index

The ordinal index of the item to extract. The first item is index 1. If the supplied index value is negative, the n^th most element on the right of the string is extracted

Delimiter

An optional delimiter. If not supplied the comma (,) is used as the default delimiter

Value if Blank

If you request an index that is larger than the number of values in the list a blank string is returned. If you prefer some other default value, then this string value with be returned.

Options

Reserved for future use

Examples

Cell A1: 34-22-32

=ELEMENT(a1,1,"-")  returns "34"
=ELEMENT(a1,1)  returns "34-22-32"  [The delimter in cell A1 is "-", not a comma]
=ELEMENT(a1,88,"-","String Empty")  returns "String Empty"  [We requested index=88 from a list that has only 3 values]