Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
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

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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