Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, October 23, 2014 9:23 AM
Hello
I need to replace three instances of a character using a calculated column. I've tried the code in this post: https://social.technet.microsoft.com/Forums/sharepoint/en-US/5487889f-5fc8-48e1-8ea2-28f8c9c8ad0c/calculated-field-error?forum=sharepointgenerallegacy
But I get #VALUE!
Anyone know what's going wrong there?
All replies (5)
Thursday, October 23, 2014 3:17 PM ✅Answered
Hi,
Please use the following formula.
=REPLACE(REPLACE(REPLACE([Description],SEARCH("-",[Description]),1,"_"),SEARCH("(",[Description]),1,"_"),SEARCH("&",[Description]),1,"_")
The above formula also works only when there is exact match of the character. If any character does match it throws #VALUE!. Please refer to the above screenshot.
Unfortunately the problem lies within "SEARCH" or "FIND" functions to find the characters. If no characters found, "#VALUE!" throws. Please refer to the following links.
Alternate option is to use the workflow or event receiver to update the field instead of Calculated column.
Please mark it answered, if your problem resolved or helpful.
Thursday, October 23, 2014 4:34 PM ✅Answered | 1 vote
Hi Sophie,
Unfortunately, Calculated column Replace function is not that much powerful as C# replace function. We have to use the SEARCH function inside REPLACE function. And also SEARCH function returns only first occurrence.
Even SharePoint 2010 Designer Workflow does not support REPLACE string, you have use the extended functionality. Please refer to the following link if you want to use the SPD workflow with extension. SharePoint 2013 Designer workflow supports REPLACE function, but SPD 2010 does not.
SharePoint Designer Workflow String Actions:
The only option left is to use the Item Event Receivers to update the field. Hope it answers all your queries.
Please mark it answered, if your problem resolved or helpful.
Thursday, October 23, 2014 3:36 PM
Hi Moktar
Thanks for your help! I've found that what that code does is add in the replacement three times on the first instance of the character rather than search through the string three times.
So if I'm replacing # for ? in a column that contains '#sharepoint #2010 #upgrade' what I get is '???sharepoint #2010 #upgrade'
I need to search through the code for the other instances - are you able to help me with that?
Many thanks
Thursday, October 23, 2014 3:59 PM
Further from that I guess what I need is an IF REPLACE then as there won't always be three instances in there - there could be one or two.
Is that even possible?
Friday, October 24, 2014 9:33 AM | 1 vote
So for anyone else struggling with this the answer appears to be NO, it can't be done.
What I've ended up with replaces two instances of # but won't work if there's only one, so I can't use it, but for anyone else who needs that, here it is:
=IF(ISERROR(REPLACE(REPLACE(fileNames,SEARCH("#",fileNames),1,addID),SEARCH("#",REPLACE(fileNames,SEARCH("#",fileNames),1,addID)),1,addID)),fileNames,REPLACE(REPLACE(fileNames,SEARCH("#",fileNames),1,addID),SEARCH("#",REPLACE(fileNames,SEARCH("#",fileNames),1,addID)),1,addID))
The column I'm checking is [fileNames], it's being checked for # and that gets replaced by the contents from the [addID] column.