Sharepoint: Calculated Column replace all spaces

Question

Seems like it would be a simple thing really (and it may be), but I'm trying to take the string data of a column and then through a calculated column, replace all the spaces with %20's so that the HTML link in the workflow produced email will actually not break off at the first space.

For example, we have this in our source column:

file:///Z:/data/This is our report.rpt

And would like to end up with this in the calculated column:

file:///Z:/data/This%20is%20our%20report.rpt

Already used the REPLACE, and made up a ghastly super nested REPLACE/SEARCH version, but the problem there is that you have to nest for EACH potential space, and if you don't know how many up front, it doesn't work, or will miss some.

Have any of you come across this scenario and how did you handle it?

Thanks in advance!

Answer 1

As far as I know there is no generic solution using the calculated-column syntax. The standard solution for this situation is using an ItemAdded (/ItemUpdated) event and initializing the field value from code.

Answer 2

Am yet to get a solution for this If you find it Let me know. Substitute is formula we use in excel which is not supported in sharepoint columns

Answer 3

Here is the method that I used to get around this issue: http://home.jordananderson.us/calculatedcolumntextsubstituation

It is not a pretty method, but it does work with certain limitations.