How can I replace ";" in a text like this with " AND " ?

example text: "abc;cde;efg;ijk" the result should be like this: "abc AND cde AND efg AND ijk"

1 Answer 1


Try this:

=REPLACE([YourColumnname],FIND(";",[YourColumnname]),1," AND ")

This should get you going in the right direction. Calculated Field Formulas

enter image description here

The column "NewColumn1" is using the exact formula as above, and was created from the SharePoint Designer. I Don't know why it did not work from the UI. I'm using the default language pack.

EDIT For sites using english as a regional setting "," work's, but any regional setting need to use ";" instead.

REPLACE function

  • thanks. I did exactly the same bit I got this ERROR: The formula contains a syntax error or is not supported. [mycolumn] type is a single line of text. this is the code at the moment: =REPLACE([mycolumn],FIND(";",[mycolumn]),1," AND ")
    – d d
    Mar 21, 2014 at 13:54
  • You should replace the "mycolumn" with the name of your textcolumn. Mar 21, 2014 at 13:55
  • of course:D mycolumn is the name of my column.the default language of our site is German. maybe I should write the German equivalent of REPLACE and SEARCH. does it make sens? also in this document I couldn't find anything about REPLACE code in the formula. isn't it strange? msdn.microsoft.com/en-us/library/office/…
    – d d
    Mar 21, 2014 at 15:14
  • I just tried lots of functions and not a single one of the microsoft standard did work. However when i entered them in sharepoint designer the one above did work out. Mar 21, 2014 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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