locked
Calculated column to replace commas RRS feed

  • Question

  • I have a text field.. I need to replace all the commas with semicolons.. And the no. of commas is also varying..

    ex. --? i love; sharepoint; i am ;; learning..
    then result should be like

    i love; sharepoint; i am ;; learning;;

    please suggest
    Tuesday, May 13, 2014 10:07 AM

Answers

  • Hello,

    I have tried this formula and it is working upto third char. If you know the fix number then you can add more REPLACE and SEARCH in below formula but if you don't know then you can use jquery/javascript to update value. Refer this link for more info:

    =REPLACE(REPLACE(REPLACE([Title],SEARCH(".",[Title]),1,";"),SEARCH(".",REPLACE([Title],SEARCH(".",[Title]),1,";")),1,";"),SEARCH(".",REPLACE(REPLACE([Title],SEARCH(".",[Title]),1,";"),SEARCH(".",REPLACE([Title],SEARCH(".",[Title]),1,";")),1,";")),1,";")
    http://social.technet.microsoft.com/Forums/sharepoint/en-US/5487889f-5fc8-48e1-8ea2-28f8c9c8ad0c/calculated-field-error


    Hemendra:Yesterday is just a memory,Tomorrow we may never see
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, May 14, 2014 7:02 AM
    Moderator
  • Hi

    Try  following syntax:

    REPLACE(div/gi,'p').

    I faced this issue on JavaScript string.replace method. Try if above syntax works for you in SharePoint calculated column.

    Wednesday, May 14, 2014 7:03 AM

All replies

  • These are only OOB options. 

    What you are looking for is text substitution. 

    http://sharepoint.stackexchange.com/questions/30531/replace-comma-with-semi-colon-in-a-site-column

    http://social.msdn.microsoft.com/Forums/sharepoint/en-US/a2f64dcd-3dc8-41da-bf03-566b5b0031d2/replace-whitespaces-in-text-string-via-calculated-fields?forum=sharepointcustomizationlegacy

    =SUBSTITUTE([FieldName]," ","")

    Tuesday, May 13, 2014 10:24 AM
  • Thanks Inder for ur reply..

    "Substitute" will not work in sharepoint

    i tried with this:

    REPLACE([my column],SEARCH(",",[my column]),1,";")

    It works for the first instance.. i need to loop it and have to fire for multiple instances.. any ideas?

    Tuesday, May 13, 2014 11:05 AM
  • Then it cannot be done using calculated columns. Use javascript.
    Tuesday, May 13, 2014 11:48 AM
  • can we get the count of "," and loop for multiple times
    Tuesday, May 13, 2014 1:44 PM
  • any ideas?

    is it injecting javascript the only way to do, or we can do through calculated column itself?

    Wednesday, May 14, 2014 4:19 AM
  • any suggestions?

    Wednesday, May 14, 2014 7:01 AM
  • Hello,

    I have tried this formula and it is working upto third char. If you know the fix number then you can add more REPLACE and SEARCH in below formula but if you don't know then you can use jquery/javascript to update value. Refer this link for more info:

    =REPLACE(REPLACE(REPLACE([Title],SEARCH(".",[Title]),1,";"),SEARCH(".",REPLACE([Title],SEARCH(".",[Title]),1,";")),1,";"),SEARCH(".",REPLACE(REPLACE([Title],SEARCH(".",[Title]),1,";"),SEARCH(".",REPLACE([Title],SEARCH(".",[Title]),1,";")),1,";")),1,";")
    http://social.technet.microsoft.com/Forums/sharepoint/en-US/5487889f-5fc8-48e1-8ea2-28f8c9c8ad0c/calculated-field-error


    Hemendra:Yesterday is just a memory,Tomorrow we may never see
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, May 14, 2014 7:02 AM
    Moderator
  • Hi

    Try  following syntax:

    REPLACE(div/gi,'p').

    I faced this issue on JavaScript string.replace method. Try if above syntax works for you in SharePoint calculated column.

    Wednesday, May 14, 2014 7:03 AM