# Formula to count items in a cell separated by a semicolon

Options

Good afternoon, could someone help me with a formula to count items in a cell separated by a semicolon and put the total in another?

I have a column called: "MRA Actives codes", in which there are different medical codes separated by a semicolon, for example:

I70.0: Atherosclerosis of aorta; I50.9: Heart failure unspecified; D68.69: Other thrombophilia;

The other column is called: "Total MRA Actives codes" and in this case I would like "3" to appear, because there are 3 codes in total.

• ✭✭✭✭✭✭
Options

This should do the trick:

=LEN([MRA ACTIVES CODES]@row) - LEN(SUBSTITUTE([MRA ACTIVES CODES]@row, ";", ""))

This calculates the length of the MRA codes cell, then does the same if the semi-colons were removed and subtracts it to get the answer on how many of them there were.

Hope this helps - if you have any issues then just post! 😊

• ✭✭✭✭✭✭
Options

If you remove the semi-colons and subtract that character count from the total character count WITH the semi-colons, that will tell you how many entries are in the cell.

=LEN(Column Name]@row) - LEN(SUBSTITUTE([Column name]@row, ";", ""))

• ✭✭✭✭✭✭
Options

Are you just wanting a formula to read what comes after the semicolon and count them based on their types?

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭✭✭✭
Options

=COUNTIF([MRA Actives codes]:[MRA Actives codes], CONTAINS("Atherosclerosis of aorta", [MRA Actives codes]:[MRA Actives codes]))

Try and see if that works for the single code.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭✭✭✭
Options

So, I just re-read your question, If you are just wanting it to count the number of codes, then just do this:

=COUNTIF([MRA Actives codes]:[MRA Actives codes], <> " ")

That will count all of the cells of that column that are not blank.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• Options

It is more about counting the codes in the MRA Actives Codes column that are separated by a semicolon, but are not always the same codes. Sorry if I didn't explain myself well and thank you very much for helping me.

Something like in the image that I have attached (I put the numbers manually, but I would like them to be generated automatically)

• ✭✭✭✭✭✭
Options

This should do the trick:

=LEN([MRA ACTIVES CODES]@row) - LEN(SUBSTITUTE([MRA ACTIVES CODES]@row, ";", ""))

This calculates the length of the MRA codes cell, then does the same if the semi-colons were removed and subtracts it to get the answer on how many of them there were.

Hope this helps - if you have any issues then just post! 😊

• ✭✭✭✭✭✭