0

Okay so I have a sharepoint list, and in this list is a Choice column where you can suggest multiple choices. I am exporting those choices to a calculated column. I want there to be a line break between each choice. I have a workflow setup to export that data but am having trouble with the calculated field. Here is what i think it should be although sharepoint wont let the formula through. 

=SUBSTITUTE(SUBSTITUTE(Specialty 2, ";#", "", 1), ";#","<br />")

Specialty 2 is the name of the choice column that i created.

Improve this question
1

1 Answer 1

Reset to default
0

Besides SUBSTITE not being available as a SharePoint Calculated Column Formula you have another problem..

If Speciality is a Choice-CheckBox Field it is not available for use in a Calculated Column

I presume that is why you state "I have a workflow setup to export that data" and assume your Speciality column is a Text column filled with the choices by the workflow.

Without a SUBSTITUTE you have to use nested REPLACE functions:

Source: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

This is up to two replacements, so separating 3 Choice values

=REPLACE(
   REPLACE(
     Specialty
     ,FIND(
        ";#"
        ,Specialty
        )
     ,2
     ,"<br>"
   )
   ,FIND(
      ";#"
      ,REPLACE(
         Specialty
         ,FIND(
            ";#"
            ,Specialty
            )
         ,2
         ,"<br>"
      )
   )
 ,2
 ,"<br>")

Takes some carefull copy pasting to get more choices in. Steps are:

  • Take a copy of this Formula
  • Paste/Replace all 4 Specialty references

Note! You can only nest function 7 levels deep! Need more than you have to use multiple Calculated Columns which do part of the original string and Concat them

Using the Workflow or CSR

I suggest you move all your replace logic into the Workflow. Much easier to do string replacements.

If you are on SP2013 you also have to look at using CSR - Client Side Rendering

But it can be done.. in SP2010 and SP2013

Provided your Speciality Column is that Text string!

And only in VIEWS, not on Forms (then use Client Side Rendering in SP2013)

You just have to do some out of the box thinking...

As you can output HTML in a Calculated Column you can also output JavaScript.. alas Microsoft disabled use of the SCRIPT tag in summer 2013, but we can execute JavaScript when an IMG has loaded..

Put this Formula in a Calculated Column and set the datatype to Number:

="<div style=text-align:left>"
&"<img src=/_layouts/images/blank.gif onload=""{"
   &"var choices='"
& [Specialty]
   &"';"
&"this.parentNode.innerHTML=choices.replace(/;#/g,'<br>');"
&"}""></div>"

This will load a blank IMG for every listitem, the onload event will execute javascript which (pre-built with your [Specialty] Choice values by the Calculated Column) replaces all separators with a BR code, then puts it in the enclosing DIV (set with align:left because datatype Number was aligned to the right)

Full explanation and more at: https://www.365csi.nl/vm365com/#/How

Improve this answer
4
  • I had some trouble following. I am assuming that you second method the Specialty field needs to be a text field correct? We have it setup as a choice column because of search issues and to prevent mistypes. Putting the formula into a calculated column in Sharepoint gave me an error even when replaced with my choice column field. Thank you for the help and i do appreciate it.
    – Falcon511
    Jun 22, 2015 at 19:29
  • Yes it needs to be Text, you can use a SharePoint Designer Workflow to extract the value from your Choice column and write it to (another) Text column
    – Danny '365CSI' Engelman
    Jun 23, 2015 at 19:13
  • Thanks for you help. The column is displaying the break point just fine on a regular sharepoint page. However when i go into my custom display form i created for the list it displays the calculated code. I tried fixing this by disable-output-escaping="yes" but to no avail that does not render the column correctly. I also tried applying some javascript to the display form below and that does not seem to work as well. I could post a picture if need be. Once again thanks for your help.
    – Falcon511
    Jun 24, 2015 at 15:46
  • The datatype=Number trick to make Calculated Columns output HTML only works on VIEWS, not in Edit and Display FORMS. On SharePoint 2013, CSR (Client Side Rendering) was added to customize the Front-End. On SP2010/2007 you have to revert back to what Christophe wrote in 2008
    – Danny '365CSI' Engelman
    Jun 25, 2015 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.