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

Eitan K
  • 121
  • 5

1 Answers1

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

Community
  • 1
Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79