1

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?

Improve this question

2 Answers 2

Reset to default
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.

enter image description here

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.

Improve this 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)))

enter image description here

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
Improve this answer
1
  • Thanks. You correctly understood my question. In the interval between asking the question and seeing your answer, I ended up writing a custom function in VBA and using that, instead. I like your answer, but I've got enough hairy functions in my spreadsheet already, that I'd prefer not to have another one, now that I have a custom function written.
    – Scott Severance
    May 3, 2019 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.