4

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?

Improve this question

2 Answers 2

Reset to default
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 &.

Improve this answer
2
  • 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 7, 2015 at 12:11
  • 4
    Try replacing all ";" with "," (semicolon to comma)
    – Christoffer
    Sep 7, 2015 at 12:13
1

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

Calculated Column Formula how to replace Substitute

Improve this answer

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.