Replace all commas to semicolons in calculated column

Question

I have an Assigned To column which look ups emails in a contacts list. What i am trying to achieve is email these set of emails using workflows but because they are not 'Person or Group' i have to make sure the emails are correctly formatted like this:

[email protected];[email protected]

rather than having commas as separator:

[email protected],[email protected]

Currently I created a calculated column , i use this formula to replace the commas with semicolons.

=REPLACE([Assigned To],FIND(",",[Assigned To]),1,";")

But this only replaces the first instance of comma, and because Assigned To column does not have a fix number of emails, if it has one email or more than 2 emails, this calculated column will not work and the workflow sending the email will product an error.

If there is more than 2 emails it will only replace the first instance of comma

[email protected];[email protected],[email protected]

If there is only 1 email, the column will not change and show up as #Name?

One possible workaround is having IF statement and counting the number of occurrence of ',' and looping for that amount of times. Unfortunately counting occurrence is also not OOTB for calculated columns.

Is there any way i can change all commas to semicolon using OOTB ways?I know using SharePoint Designer 2013 workflows might be able to do this but my company only has 2010 workflows and i have no authorization to download 2013 workflows. I am not familiar with VS so i might need step-by-step guide on VS event receiver etc.

Thanks in advance !!

Answer 1

You can still use SP 2010 Workflow to run all those conditions. It should be simpler.

The ideal excel formula should be SUBSTITUTE but that isn't supported. Hence I would get this done in an SP2010 Designer workflow.

Answer 2

You don’t need any extra operation to change "," to ";".

In SharePoint Designer workflow, we can return the lookup field as "Lookup Values, Comma Delimited" (works in 2010 workflow and 2013 workflow).

Besides, to enable SharePoint 2013 Workflow platform, you need to install and configure Workflow Manager.

For sending emails to external email addresses, you need to use SharePoint 2010 workflows instead of SharePoint 2013 workflows because of the security enhancement in SharePoint 2013 workflows.

How to send emails to external addresses in SharePoint:

https://blogs.technet.microsoft.com/rajbugga/2013/10/15/sharepoint-2013-unable-to-send-emails-to-external-email-addresses-sharepoint-2013-workflows/