Create a new column with the number of occurences of a delimiter in another column

I have a list of transactions. In one column, there are various numbers of lawyers, each separated by a semi-colon. In another column, there is a transaction value in $. Before unpivoting the lawyers, I want to adjust the transaction value according to the number of lawyers for that record. In that table below, column A and column B are in table. I would like add the calculated column D (with or without adding column C).

For example:

Column A Column B Column C Column D
Lawyer1 ; Lawyer2 ; Lawyer3 90 3 30
Lawyer1 ; Lawyer2 ; Lawyer3 ; Lawyer4 100 4 25

Hi Ken. You can get the results for column C by adding a custom column with the following formula:


That will count the length of the string of lawyers and then subtract the length of the same string minus the semi-colons. Since there is always 1 less semi-colon than the number of lawyers in the string, add 1 to the end of the formula.

If it helps with future formulas, I sometimes try to figure out how to do something with regular Excel formulas first, then just look up the Power Query equivalents and convert it. You can find many more formula examples in Google looking for Excel than you can Power Query.

Thanks SteelReyn. I will try it out and let you know how it works.

Ken

or.... use this step below
Regards

SteelReyn, thank you very much. I tried it and it works perfectly.

Bill, I am very new to Power Query and very unfamiliar with the M language. I don't know what "Result" refers to. Is the code to be pasted directly into the Query Editor. Also, I had indicated columns C and D as two separate columns. What does "ColumnCD" mean?

See attachment, I hope everything will be clear.

Regards