SharePoint 2013 Find/Replace Calculated Column

Question

Was hoping someone could assist me with regards to SharePoint Calculated column.

I have an address column which has an &. We need to find and replace the & with and.

The problem is that the & is in different places all the time depending on the length of the address.

Is there something I can do in SharePoint 2013 to assist me?

score 4 · Accepted Answer · answered Sep 07 '15 at 11:36

4

In Excel this can be solved with =SUBSTITUTE(). However, this formula is not available in SharePoint and you have to use =FIND() and =REPLACE() to replace =SUBSTITUTE().

If you only have one & (question is implying that this is the case), this can be easily achieved with =REPLACE([column];FIND("&";[column]);1;"and"). If you have to replace more than one &, you have to nest this code as many times in your function, as you expect a &.

answered Sep 07 '15 at 11:36

wheeler

443
5
22

ok when i try it out it says "The formula contains a syntax error or is not supported." Seems the formula is right but i am doing something wrong – Aadil Ganie Sep 07 '15 at 12:11
4

Try replacing all ";" with "," (semicolon to comma) – Christoffer Sep 07 '15 at 12:13

score 1 · Answer 2 · edited Apr 13 '17 at 12:41

1

How to replicate the SUBSTITUTE() function behaviour with default SharePoint functions was explained here:

Calculated Column Formula how to replace Substitute

edited Apr 13 '17 at 12:41

Community

1

answered Sep 07 '15 at 11:45

Danny '365CSI' Engelman

21,176
7
35
79

SharePoint 2013 Find/Replace Calculated Column

2 Answers2

Linked