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?

  • 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. – mhorn30 Feb 13 '13 at 8:52
  • Any difference in the Excel installation between the environments (installed/not installed, SP applied, other hotfixes etc)? – SPArchaeologist Feb 13 '13 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. – SPArchaeologist Feb 13 '13 at 8:55
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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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