1

How can I replace the space in between a file name in a calculated column in SharePoint list 2016?

Example I have a file name that contains ABC 123 and I want a calculated column to return the value ABC/123.

4
  • Are you trying to create a calculated column in document library & use the SharePoint default file "Name" column in formula? May 20, 2021 at 7:01
  • Hello Ganesh, No, I have created a list library in SharePoint. I have a column field that contains a file name and because I cannot insert a "/" in the file name, I want to create a calculated column that contains the "/". As mentioned I want to convert ABC 123 to ABC/123 in a calculated column. Rgds
    – NCA
    May 20, 2021 at 7:15
  • In SharePoint, List & library are different. Did you create a list or a document library? By your description, I think you have created a document library to store the documents/files & you want to use the SharePoint default file "Name" column in calculated formula & replace the spaces. Or did you have a custom column/field which stores file name? May 20, 2021 at 7:20
  • @NCA. The point is, for the default "Name" column for files in a document library, calculated column will not be able to get its text value.
    – Jerry_MSFT
    May 20, 2021 at 7:21

2 Answers 2

0

If you are trying to use the SharePoint default file "Name" column (see below image) in calculated formula, then it is not possible.

enter image description here

It's a known limitation of SharePoint. We cannot use file name in calculated column formula.

There is a SharePoint UserVoice for this. You can vote here: Use filename in calculated field

Workaround:

  1. Create a new text field & populate its value using workflow on item creation/update. Hide this field from property forms.

  2. Use workflow to updated the Title field based on the filename. Then use Title field in the calculated column formula.

1
  • I have been able to sort it out but I am now facing another issue. I would like to set a validation so that the users are forced to name the files as per the an established name convention. I only want the users to name the files in the document library not more than 13 characters. The field name is "Name" in my document library.
    – NCA
    May 20, 2021 at 15:00
0

Using JSON formatting, we can do part of your requirements. I am not sure if there are a bunch of spaces inside the file name. If we are sure only one space needs to be replaced, we can use a simple JSON formatting on this. The replace function only works for the first instance of the specified value. Here is the white space.

A demo here:

enter image description here

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "span",
      "txtContent": "=replace('[$FileLeafRef]',' ','/')"
    }
  ]
}

And it will not work for the condition when we have multiple white spaces inside the file name. For that, I will suggest you to use a flow to directly get the file name, loop through the string and replaces all the white space with "/". And update the new new value to another single text column.

5
  • JSON formatting is not supported in SharePoint 2016. May 20, 2021 at 7:39
  • Hi, That's exactly what I wanted!! Thanks a lot for your help.
    – NCA
    May 20, 2021 at 7:49
  • One more thing please, how can I remove the file extension ".pdf" at the end of the file name ?
    – NCA
    May 20, 2021 at 7:52
  • @NCA, Just want to confirm if you are using SharePoint 2016 or SharePoint Online? Just like Ganesh said JSON formatting is not supported in SP2016. It only works for Online and 2019. If you want to remove the extension, JSON formatting can do and I will comment an example below. And for 2016, I think workflow will be the best choice to handle the string.
    – Jerry_MSFT
    May 20, 2021 at 9:10

Your Answer

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

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