I have an Excel sheet including columns like this:
| 143 | 423 | | | 423;234 | 134;234;143 | =SOME_FORMULA() |
The data have the following characteristics:
- Although numeric, they should be considered strings, since they're IDs. The columns are formatted as text.
- Any column may contain zero or more ID numbers. If there is more than one ID number in a column, they'll be semicolon-separated.
- The cell marked
SOME_FORMULA()
should contain a count of all the ID numbers on the row. In this example, the cell should display7
.
In searching for how to split a string on a delimiter, all the answers I've found have relied on assumptions that don't match my data, viz. they assume that the number of elements in a string is known in advance and/or they try to split the text out into a fixed number of columns (which again assumes that the number of elements is known in advance).
How can I calculate what I'm looking for?