1
=IF(Title<>"";"<a href='/test/"&SUBSTITUTE([Title]," ","")&SUBSTITUTE([Title],"/","")>Info</a>";"#")

I'm trying to create a link based on two coloums. Is there a way to remove " " and "/" in title? All I get is syntax error!

I'm trying to do this without having to clone "title" column just to remove the space and slash.

1

A lot is wrong in your code.

SUBSTITUTE does not exist for Calculated Columns

Besides that, you are mixing separators, using ; in your if statement (international notation) and , in your substitute

You CAN however have a Calculated Column outputted as HTML

PROVIDED YOU SET THE DATATYPE TO NUMBER

Then it is a matter of doing the replacement after the user clicks (so you can use Javascript) instead of doing it up front

="<a href='#' onclick=""alert('"
 &Title
 &"'.replace(/ /gi,'').replace(/\//gi,''))"">"
 &Info
 &"</a>"

Notes

  • Besides the references to (exsiting columns!!!) Title and Info it is all one string; so no worries with using ; or , parameters on international systems
  • SharePoint strings start or end with ", so an escaped quote "" is needed to open de onclick definition and NOT close the SharePoint string. Using a single quote here would make the single quote unavailable in the JS code
  • The Title is wrapped in single quotes so the JScode treats it as a String; otherwise Javascript would look for a variable or function named in this rows Title field.
  • The RegEx replace can be done much smarter but would be unreadable if you're not familiar with RegExs, so I left the 2 calls

replace the

alert( X )

with

document.location = X

to make it work like a link

and off you go

Wrapping an IF around it:

=IF(ISBLANK(TITLE),"","<a href='#' onclick=""alert('"
  &Title
  &"'.replace(/ /g,'').replace(/\//g,''))"">"
  &Info
  &"</a>")

Notes

  • the IF formula parameter separator here is a , (comma) test to see if you need a ; (semi-colon) on your local system But only the ones in the ,"", SharePoint part!! The other commas are inside a string and Javascript interpreted
  • I left the i (ignore case off the Regular Expressions search) not needed for space and /

Be Aware this is using the undocumented behaviour of setting a Calculated Column to datatype Number; if Microsoft changes this it might not work any more and you would have do to it the CSR/JSlink way. But .. this method is less stressfull on both Server and Client; major difference being this code is downloaded for every ListRow whereas CSR code is downloaded once then called from every row. With just a few characters like in this link example, this approach is faster than CSR

1

In your code, there is a " missing before >Info. But anyway, calculated column don't allow HTML, because such thing would introduce security risk.

So I would recommend trying CSR instead.

In short, you'll have to create a small JavaScript file, upload it to portal, and attach it to your list via e.g. JSLink (Edit page -> Edit webpart -> Miscellaneous -> JSLink).

The code in the js file should be something like this:

SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
  Templates: {
        Fields: {
           'LinkTitle': {'View':function(ctx) {
                  var title = ctx.CurrentItem.Title.replace(" ","").replace("/","");
                  var url = String.format('{0}&amp;ID={1}', ctx.displayFormUrl, ctx.CurrentItem.ID);
                  return String.format('<a class="ms-listlink ms-draggable" href="{0}" onclick="EditLink2(this,{1});return false;" onFocus="onLink(this)">{2}</a>', url, ctx.ctxId, title);
           }}
       }
  }
});

Detailed article about CSR, very good for beginners, can be found on CodeProject:

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.