# Calculated Column Formula how to replace Substitute

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).

• Do you only want to display it in a View (so not the edit/display form)? Or do you need to continue with the calculated value in another formula? Commented Jul 17, 2015 at 8:25

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.

• Danny, the columns will increase if he needs more than 4 dashes. Maintainability will be a pain. Commented Jul 17, 2015 at 10:02
• Roland specfically said: "strings can have up to four "-"" You can go up to 48 Calculated Columns, creating 48 Cn Formulas (with just 3 minor changes each) takes you.. 1 hour? Commented Jul 17, 2015 at 10:55
• Ah right. Didn't see that. Thanks for clarifying :) Commented Jul 17, 2015 at 10:57
• Thanks for the in depth answer. I actually managed to come up with a bit of a hack (see OP for edit). I forgot to mention that this was going to be used as a one off as I am transferring files form an old server which kept all of the meta data in the name so maintainability was never an issue. Commented Jul 20, 2015 at 0:13

Could you use `REPLACE()` here (documentation)?

• I think I know how to do it logically.. But not how to write it. Find everything on the left of the last "-" and replace it with "" (effectively deleting it). What is left should be (THIS VALUE). Is this correct and if so could add the formula to the answer? Commented Jul 17, 2015 at 4:39
• @Roland Same here. I did make a small attempt before posting so I could give you the formula but without it in front of me it's difficult to conceptualize. Hopefully somebody smarter than I can help improve this answer with a working example! Commented Jul 17, 2015 at 4:41

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.