2

I would like to find the number of values or commas in a single line of text column. For example if the entry is 1,5 ,10 I would like the count to return 3 (or 2 if counting commas).

I have tried using this logic:

LEN([Probe Numbers]) - LEN(SUBSTITUTE([Probe Numbers],",",""))

But there is no substitute in SharePoint. I have also tried using this instead of substitute REPLACE([Probe Numbers],FIND(",",[Probe Numbers]),1,""), but that is only replacing the first comma not all, so the value won't be correct when subtracting lengths.

I cannot use InfoPath, trying to do this using just a calculated column

2
  • Do you want the count displayed in a View only, or do you need the count in other formulas or workflows? – Danny '365CSI' Engelman Dec 7 '16 at 8:34
  • In a column / calculated field – Eitan K Dec 8 '16 at 13:34
1

You have to nest the REPLACE, replacing one occurrence every time.

I think you can nest 7 levels deep in one formula, if you need more spread the workload over multiple Calculated Columns

I have a private tool to built complex Formulas, if you do it step by step and working from a good text-editor you can do this by hand

If you only want to display the value you can use JavaScript

Calculated Column Formula how to replace Substitute

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.