Haven't found a solution on this even though it seems like it should be cut and dry.

I have a column containing characters "-" (e.g., XXX123-123-fjhrh-sdafsdfsd). I want to find every "-" and replace it with "@". Seems easy but so far haven't found a solution. My calc column always seems to stop at the first occurence of "-" and only replace that one. Seems every forumula wants you to tell SharePoint where to start (position). You can't simply tell SharePoint, Find this "-" ALL OCCURENCES and replace with this "@" ALL OCCURENCES. I don't have the option of saying start at this position because my data is not the same in every cell/field (e.g., number of characters).

Closest I've gotten is this:

=REPLACE([IN THIS COLUMN],FIND("-",[IN THIS COLUMN]),LEN("-","@")

RESULTS:

XXX@123-XXX-123

Hi JJSHAREP,

If there are only three - in the string for every value in the column, then you can use the formula below for the calculated column:

=REPLACE((REPLACE((REPLACE([IN THIS COLUMN],FIND("-",[IN THIS COLUMN]),1,"@")),FIND("-",(REPLACE([IN THIS COLUMN],FIND("-",[IN THIS COLUMN]),1,"@"))),1,"@")),FIND("-",(REPLACE((REPLACE([IN THIS COLUMN],FIND("-",[IN THIS COLUMN]),1,"@")),FIND("-",(REPLACE([IN THIS COLUMN],FIND("-",[IN THIS COLUMN]),1,"@"))),1,"@"))),1,"@")

If the number of the - for every value in the column, then I recommend to use code to achieve this goal.

Best regards.

Hello and thanks for your response,

The problem is that the number of "-" varies. Some fields have 1 occurence others have 3 or 4. If I set the formula to handle 3 occurences and a field only has 1 occurence, will that cause and issue? Should I set it for the highest number of occurences even if it's NOT consistent?

In terms of code. I just posted a question about how to do this in the XSL using "translate". No response yet.

Hi JJSHAREP,

The formula only works when there are only three "-" in the column value, and the formula will fail if there are more than or less than three "-" in the column value.

I recommend to use Jquery code to replace the characters in the column called IN THIS COLUMN in the UI:

<script src="http://code.jquery.com/jquery-1.10.2.min.js" type="text/javascript"></script> 
<script type="text/javascript">
    $(function () {
       var Index=0;
       $(".ms-listviewtable tr th").each(function(i){ 
       	if($(this).find("div").attr("name")=="IN_x0020_THIS_x0020_COLUMN"){
           Index=i;
       	}
       });
       $(".ms-listviewtable tr").each(function(i){
        if(i>0){
        var value=$(this).find("td").eq(Index).text();
        var value1=value.replace(/\-/g, '@');
       			$(this).find("td").eq(titleIndex).text(value1);
       			    }   
       		       });             
    });
</script>

Best regards.

Thanks