OPA_T#973_Sept-05-2017
Excel Tip: Counting the number of items in a multi value cell
Some iSearch fields contain multiple values when downloaded, for example, the number of
Authors for a Publication, as shown in the example below.
There are ways to answer these questions using iSearch. The unwind feature available in the
.csv download enables the user to create a row for each value in a multi valued cell but this
increases the size of the dataset and may not be helpful depending on the analytical question
being investigated. There is also a “count of pubs” field in iSearch: Grants that that can be
included in a download.
The method and formula
The individual values are separated by a ‘;’ so to know how many unique values there are it is
possible to count the number of times ‘;’ appears in the cell and add one to the total (for the
last value in the list).
There is no simple formula to count the number of times a character appears in a cell, but it is
possible using a combination of Excel formulas:
The LEN formula counts the total number of characters in a cell.
The SUBSTITUTE formula replaces a specific character in a cell with another character
So counting the number of characters in a cell and comparing that to the count without any “;”
(it is replaced by “” i.e. removed) gives the number of times “;” appears in the cell. Note that
the last value is not followed by “;” so it is necessary to add one to the result.
Formula 1: =LEN(D2)-LEN(SUBSTITUTE(D2,";",""))+1
This formula works for any field where every record has at least one value (see below, counting
the number of PIs on each grant.