Excel
Support
Documentation
Online POS
More

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 nth 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]