Calculated Column Formula how to replace Substitute

Question

I need to grab everything after the last "-" (strings can have up to four "-") and if there is a space after the last "-" to not take the space.

Examples:

TEXT -(THIS VALUE)

TEXT - TEXT -(SPACE)(THIS VALUE)

TEXT - TEXT - TEXT -(THIS VALUE)

Should return: "(THIS VALUE)"

I managed to get this formula for Excel which works: =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255)) However I know that SP won't allow SUBSTITUTE. Is there a way of doing this in SP?

--

EDIT: I ended up using this:=TRIM(REPLACE(TRIM((RIGHT(A1,FIND(" -",A1)-1))),1,0,"")). Please note that this is not perfect (if no space after - it leaves the -)

I chose Danny's answer as that method is a great way of solving the substitute method for long term use (I only need a one off hack however).

Answer 1

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.

Answer 2

Could you use REPLACE() here (documentation)?

Answer 3

There is no easy way to do this using calculated columns in SharePoint. Here, you will have to find the last occurrence of "-" and then get the value after that using RIGHT(). To find the last occurrence, you will have to loop through the column value and maintain a count of occurrences. There is no function available in SharePoint to do that.
If you want to use calculated columns, the only way to do this would be to combine multiple calculated columns to SEARCH and REPLACE the previous calculated column's value which I think wouldn't be feasible.

Instead, if you just want to show the value without using it, you have 2 options.

• 1 approach would be to use Data View Web Parts. Recursion is possible in XSLT so you can create a template which gets substring-after "-".

• Yet another approach would be to use CSR (Client Side Rendering) to modify the HTML and show the value before the list is rendered using Javascript.