Formula to count items in a cell separated by a semicolon
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.
Best Answers

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

If you remove the semicolons and subtract that character count from the total character count WITH the semicolons, that will tell you how many entries are in the cell.
=LEN(Column Name]@row)  LEN(SUBSTITUTE([Column name]@row, ";", ""))
Answers

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

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

So, I just reread 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."

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)

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

If you remove the semicolons and subtract that character count from the total character count WITH the semicolons, that will tell you how many entries are in the cell.
=LEN(Column Name]@row)  LEN(SUBSTITUTE([Column name]@row, ";", ""))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!