3

I have a calculated column with the formula:

=IF(ISNUMBER(FIND("%20",[URLColumn])),REPLACE([URLColumn],FIND("%20",[URLColumn]),3," "),[URLColumn])

This formula works fine on our DEV machine and another SharePoint site (on two different web applications) and also in Excel. Therefore the syntax must be correct.

When I put this formula in a calculated column on the Production SharePoint site (on yet another web application), I get the error: "This formula contains a syntax error or is not supported"

I've narrowed down the issue to the "REPLACE" function (IF, ISNUMBER and FIND seems to work fine; I don't get any error message on the Production SharePoint site when I create a calculated column with one of these functions).

Any suggestions? Is there some way to "active/enable" the "REPLACE" function? Or what can be different between the web applications, for it to work on two but not on the third?

Improve this question
3
  • 1
    So, I can't answer my own questions withing 8 hours, but here it is: So, after going stark raving mad for not being able to solve this, I decided to open the list in SharePoint Designer and add the column from there. .... and it worked without problems. The exact same formula that didn't want to work via the normal SharePoint List Library Settings interface worked perfectly via SharePoint Designer. I won't ask questions; I'll just accept it. Commented Feb 13, 2013 at 8:52
  • Any difference in the Excel installation between the environments (installed/not installed, SP applied, other hotfixes etc)? Commented Feb 13, 2013 at 8:53
  • .. disregard the above, seems you got it working. Anyway, I would guess there is something going with that function.. I would expect to see it on this page but it seems it is missing. Commented Feb 13, 2013 at 8:55

2 Answers 2

Reset to default
0

So, after going stark raving mad for not being able to solve this, I decided to open the list in SharePoint Designer and add the column from there. .... and it worked without problems. The exact same formula that didn't want to work via the normal SharePoint List Library Settings interface worked perfectly via SharePoint Designer. I won't ask questions; I'll just accept it

Improve this answer
0

Excel function can be affected by localization/language settings.

Search if the 'REPLACE' function has a localized name (example: in Dutch the SUM() function is SOM() )

Once I also encounter problems with ; and ,. Depending on localisation you should use ; or , to separate parameters in Excel functions.

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.