Sharepoint - Calculated Column Formula how to replace Substitute

There is no SUBSTITUTE function for SharePoint Calculated Formulas, and the REPLACE function is not a global replace.

See https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List for the complete list of Functions available

So you will have to use a cascading approach to take care of every dash in your text.

First step is C1 which takes your Text column Col as input:

=IF(ISNUMBER(FIND("-",Col)),MID(Col,FIND("-",Col)+1,255),Col)

Second step C2 takes C1 as input:

=IF(ISNUMBER(FIND("-",C1)),MID(C1,FIND("-",C1)+1,255),C1)

You mention a maximum of 4 dashes, so you need a C3 and C4 as well;

Your final function ColLast will take care of the optional space you mention:

=IF(LEFT(C4,1)=" ",MID(C4,1,255),C4)

CalcMaster Editor

I have a (private use only) Calculated Column Editor; so all Formulas look like:

You can merge (copy/paste) all the C columns into the next function; you see this happening in the C4 function above where (in blue) C3 is replaced with the complete Formula. My CalcMaster does it with one click.. by hand you have to very carefully copy/find/replace by hand.

BUT, stuffing everything in one Formula only referecing Col creates a Formula with 14574 characters...way over the 4000 character limit for a Calculated Column.

So this option is only possible with multiple cascading Calculated Columns only.

Note: a slimmed down version of my CalcMaster is on GitHub: https://github.com/Danny-Engelman/CalcMaster It doesn't do any fancy stuff, only helps in automatically saving your Formula on every keypress and providing immediate error feedback.

The ViewMaster 365 way

Provided you only need this in a View and can live with the (minor) sideeffects

You can create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.split('-').pop().trim()"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:https://www.365csi.nl/vm365com/#/How

This is easier implement than CSR, but CSR can do it for you on Forms as well.