Counting commas between multiple specific strings of text in a single cell

[Country_Calc_2]

The objective is to count the total number of investigators per country.

All source text is in a single cell.

Here is the basic logic...

Find Country Name "*Argentina*"

Count the number of commas between the country name and the next colon ":"

Then add 1.

Argentina has 8 commas + 1 = 9

A colon was added manually to count the last country.

Principal Investigators Argentina: J.L.C. Navarro Estrada, E.A. Duronto, S.M. Macin, M.S. Trivi, F. Paoletti, J.P. Albisu Di Gennaro, S.A. Sassone, J.A.A. Egido, and E. Hasbani. Australia: J. Rogers, S. Nicholls, J. Amerena, D. Walters, G. Oldfield, D. Eccleston, G. Nelson, and P. Shetty. 4 Austria: K. Huber, U. Hoppe, and T. Sturmberger. Belgium: D. Schoors, C. Borin, I. Buysschaert, F. De Man, J. Voet, P. Debruyne, F. De Vroey, A. De Wolf, S. Verheye, and J. Castro Rodriguez and G. Flores-Vivian. Brazil: A.A.V. de Paola, L. Moreira Baracioli, L.F. Leite Tanajura, L.N. Maia, S. Zimmermann, O. Dutra, R. Botelho, and D.B. Précoma. Bulgaria: F. Nikolov, E. Docheva, S. Marchev, S. Dzhurova-Stoyneva, Z. Kamenova, I. Petrov, and C. Velikov. Canada: F. Ayala-Paredes, A. Cheema, M. Hartleib, R. Labonte, M. Madan, S. Mehta, G. Martucci, L. Bilodeau, J. Ducas, F. Charbonneau, O. Bertrand, A. MacDougall and I. Billingsley.:	Argentina	9
	Austria	3
	Belgium	10
	Brazil	7
	Canada	11

 

[Country_Calc_2]

No comment about the Power Query Solution?

Sorry, I missed this earlier. I have not used the power query tool yet and it will take me a little time to figure it out.