Forum Discussion

ard-qua's avatar
ard-qua
Copper Contributor
Aug 31, 2023

Calculated column that combines all values from three columns into one with break line.

In Sharepoint, I create a calculated column that combines all values from three columns into one with break line.

=[AOR 1:]&" "&[AOR 2:]&" "&[AOR 3:]

The result is shown in one line. "AAA BBB CCC "

I think this is because the data type is Single Line of Text. It would be nice if the type can be selected as Multi-line Text, then I could use the Char(10) in the formula.

 

1) Is there a way for the calculated column to show results with break line?

ie:

AAA
BBB

CCC

 

  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Sep 06, 2023

    ard-qua you can do this by formatting the calculated column in advanced mode with some simple JSON. My  columns are named First, Second and Third and the calculated column is named Calc. The formula for that column is =First&" "&Second&" "&Third and without the JSON column formatting it will put it all on the same line.

    Format the calculated column in advanced mode, delete the JSON code that's currently there and paste in the following:

     

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=[$First]+'\n'+[$Second]+'\n'+[$Third]",
  "style": {
    "font-weight": "bold"
  }
}

     

     

    This is the result:

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Sep 06, 2023

    ard-qua Which version of SharePoint are you using?

     

    In past, HTML was working with the SharePoint calculated column formulas. But, it does not work anymore. Previously we were able to do it using simple formula like: 

     

    =CONCATENATE("AAA","<br/>","BBB","<br/>","CCC")

     

    This unsupported use of HTML in calculated column formula is now deprecated by Microsoft. You can find more information at: Handling HTML markup in SharePoint calculated fields 

     

    So, if you just want the new lines for display purpose in list view, use JSON formatting as suggested by Rob_Elliott.

     

    If you need to store the actual new line value in column and use it later somewhere else, you will have to use Power automate or Power Apps to update the field value with new lines whenever new item is created or updated in the list

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources