Formula to count items in a cell separated by a semicolon

VirginiaLazor

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.

Thanks in advance.

Nick Korna

Hi @VirginiaLazor,

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! 😊

Paul Newcome

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, ";", ""))

Kleerfyre

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

Kleerfyre

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

Kleerfyre

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.

VirginiaLazor

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)

Nick Korna

Hi @VirginiaLazor,

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! 😊

Paul Newcome

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, ";", ""))