You might be able to create a calculated field that counts the number of semicolons, then aggregate. If you could share your data or a packaged workbook, we could show the actual syntax.
Please find below a sample. As you can see in the "investors" column, I can have a value with more than one name e.g. "BZPlan; Fir Capital". My objective is to be able to count this row as one round of investment for each of the two investors.
p.s. I tried Splitting the Investors column but I can only get the first split to count - can't think of a straightforward way to count the other occurrences of the same name in the next Split-n columns (like "Undisclosed Investors")
round round_date amount investors Seed VC - II 14/03/2017 0.6 NDC Innovation Fund;Travel Capitalist Ventures Seed 18/02/2016 0.11 Boost VC Seed 03/05/2018 0.26 FINEP;Rede de Investidores Anjo de Santa Catarina Seed 02/02/2018 0.5 Undisclosed Investors Seed 28/09/2016 0.12 Techstars Secondary Market 17/07/2018 Banco Bradesco;Launchpad Accelerator;Verus Group Grant 08/06/2018 0.8 Google.org Seed 19/12/2017 0.3 BrazilLAB;Undisclosed Investors Seed VC 07/05/2018 0.56 BZPlan;Fir Capital Grant 29/08/2016 0.35 LaunchVic
OK, I see. You can create a calculated field to count the number of semicolons as follows:
// Calculate the length of the string without ; then subtract...
// ...from the length of the original string.
See attached workbook.
Investors.twbx 32.3 KB
Thank you for the tip above. It does give me a count of investors per round, which will be useful to see the distribution of how many investors usually go into a Series A or B for instance, by region.
Apologies if my initial question wasn't clear as to what I was trying to achieve. The idea is to aggregate per investor, all the rounds they've been involved in. That means, if the investor was a sole investor in let's say, 50 rounds - I get the count from Split 1. But if this same investor is a co-investor is other rounds with other investors (and thus appears in the subsequent splits - 2+) then I can't count the rounds this investor has been in with others using the above method.
Example of Breega Capital below - in effect, they've been involved in two rounds of investment (two different companies) but I can only count one in each split.
Maybe I'm approaching it the wrong way as well. Any thoughts most welcome