0

is there any function that can delete or erase all numbers in a single column?

example: i want to erase all zeros(0)

January0
0February
0March
April0
wo0rd

and results should be:

January
February
March
April
word

  • Do they really need to be deleted from the Field Value, or are you fine when only in the View display they are replaced? – Danny '365CSI' Engelman Apr 27 '17 at 8:37
2

It depends on what you need

Replace characters in the View or Form (SP2013/SP2016/SPO)

This can easily be done with CSR (Client Side Rendering)

  • User the Cisar WYSIWYG CSR editor
  • one simple function for use on Views/Forms

    var fieldvalue=ctx.CurrentItem[ctx.CurrentFieldSchema.Name];
    return fieldvalue.replace(/0/g,'');
    

Replace characters in value (Calculated Column) (SP2003 - SPO)

SharePoint has no SUBSTITUTE function, and the REPLACE function only replaces ONE string instance

So all you can do is nest FIND & REPLACE and merge them into one Formula

(so Output_1 to Ouput_6 are not required)

Output_7:

=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,""),FIND("0",REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,"")),1,""),FIND("0",REPLACE(REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,""),FIND("0",REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,"")),1,"")),1,""),FIND("0",REPLACE(REPLACE(REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,""),FIND("0",REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,"")),1,""),FIND("0",REPLACE(REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,""),FIND("0",REPLACE(REPLACE(Input,FIND("0",Input),1,""),FIND("0",REPLACE(Input,FIND("0",Input),1,"")),1,"")),1,"")),1,"")),1,"")

Alas SharePoint can only nest 7 levels, so you can't merge Output_7 into Output_8

Output_8 needs to start from Output_7 Calculated Column (replace Input with Output_7 in the above Formula)

Hacky alternative, replace in View only

using JavaScript in a Calculated Column (SP2013,16,SPO - except Modern Experiences)

Provided you only need this in a View and can live with the (minor) sideeffects

You create only one Calculated Column, set to output as Number with the Formula:

="<img src=/_layouts/images/blank.gif onload=""{"
 &"var TD=this.parentNode.parentNode;"
 &"TD.innerHTML='" & Col &"'.replace(/0/g,'');"
&"}"">"

This will rework Col every time it is displayed in a View, so you can't use the "Calculation" anywhere else.

Explanation and why JavaScript in a Calculated Column (in a VIEW!) works is here:http://www.viewmaster365.com/#/How

This is easier implemented than CSR, but CSR can do it for you on Forms as well.

| 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.