Excel: Split a string on a delimiter and determine the length of the resulting array

Question

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 display 7.

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?

Answer 1

Just wanted to add a simpler approach. The question describes cells as being blank or containing one or more IDs. If there is more than one ID in a cell, the additional IDs are preceded by a semi-colon. So you can get the count of IDs from the count of non-empty cells plus the count of semi-colons.

The formula in G1 is:

=COUNTA(A1:F1)+SUMPRODUCT(LEN(A1:F1)-LEN(SUBSTITUTE(A1:F1,";","")))

COUNTA gives you the count of cells with at least one ID. The rest of the formula finds the number of semi-colons by comparing the length of everything to the length of everything without the semi-colons.

Answer 2

I am not too sure if you are ultimately looking for that SOME_FORMULA() or way to split a string on a delimiter.

I however suggest a possible solution for that SOME_FORMULA()

Assumptions

• Your data is formatted as Text

• The number of columns is fixed. The rest cells to the rightmost may be left blank

  In this example sample data is in cells C3:J12

Now in K3 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula shall now be enclosed in curly braces to indicate that it's an array formula.

Drag it down up to the intended rows.

=SUM(IF(LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";",""))>=1,IF(C3:J3=";",0,IF(RIGHT(C3:J3,1)=";",LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";","")),LEN(C3:J3)-LEN(SUBSTITUTE(C3:J3,";",""))+1)),IF(ISBLANK(C3:J3),0,1)))

Some validations & limitations in this solution

• Even if you put an extra ; after the last entry in a cell it will be ignored
• Blanks are considered 0
• Multiple ; one after the other will fail this validation and count of IDs will be increased by one for every subsequent recurring ; so avoid data like 234;567;;
• Non blank cell but that contains only a space will still get counted as 1