0

I have a single line of text column in my list called "Invoice Number" which only needs to contain numeric characters (0-9).

I have attempted using column validation with =ISNUMBER([Invoice Number]+0)

However using this formula still allows for the use of symbols. As long as there is at least 1 number in the text box it will allow you to save the item with the following symbols - . + £ $

everything I have found online suggests using the formula above but is there anyway to tweak this formula to prevent the use of these symbols?

1

Two solutions come to mind...

Check for the problem characters:

=AND(
     ISNUMBER(0+[Invoice Number]),
     ISERR(FIND("$",[Invoice Number])),
     ISERR(FIND(".",[Invoice Number])),
     ISERR(FIND(",",[Invoice Number]))
    )

If the invoice number is a known length, check for the valid characters. This checks the first four characters:

=NOT(OR(
        ISERR(FIND(MID([Invoice Number],1,1),"0123456789")),
        ISERR(FIND(MID([Invoice Number],2,1),"0123456789")),
        ISERR(FIND(MID([Invoice Number],2,1),"0123456789")),
        ISERR(FIND(MID([Invoice Number],4,1),"0123456789"))
     ))

Regular Expressions would be a real nice addition to SharePoint formulas!

0

Why haven't you used Number column? You can have min and max value set to 0 - 9 there. SharePoint will validate it by default.

  • Need to avoid commas and decimal points being added to the number for example number column tends to show as 1,000 rather than 1000. The numbers being recorded aren't number values and need to show as the latter. I'm not the most experienced when it comes to SharePoint so if this is achievable using the number column could you please provide instructions on how and I'll attempt it this way. – Kirito Oct 22 '18 at 13:56
  • 1
    If you want to avoid columns and validation on single line isn't working for you, then I would suggest you to use this number column on the form for min-max validation and then create a single line text CALCULATED column which copies the value of number column into itself. And use this calculated column for displaying purposes. – harshal gite Oct 22 '18 at 14:02
  • This was one of the other solutions I came across however I wanted to avoid creating additional columns. I'll run this by my team leader and see if this is an acceptable work around. Thank you for your help! – Kirito Oct 22 '18 at 14:11
0

Add this code to a Code Editor Web Part below the list form in your NewForm or EditForm:

[javascript]
<script type="text/javascript" src="/test/English/Javascript/jquery-1.4.2.min.js"></script>
<script type="text/javascript">
fields = init_fields_v2();

// Allow numbers only in text fields
// Array of FieldInternalNames to lilmit to numbers only
var arrToCheckForNum = [‘Title’,’Num’];
// Array of length of input string – set to 255 if you do not want to limit input length. Corresponds to the same array index in the "arrToCheckForNum"
var lengthOfInputNumber = [9,2];

$.each(arrToCheckForNum,function(idx,item){
$(fields[item]).find(‘input’).css({‘width’:’75px’});
$(fields[item]).find(‘input’).keyup(function(e){
var thisVal = $(this).val();
thisVal = thisVal.substring(0,lengthOfInputNumber[idx]);
$(this).val(thisVal.replace(/[^0-9]/g,”));
}).blur(function(){
var thisVal = $(this).val();
thisVal = thisVal.substring(0,lengthOfInputNumber[idx]);
$(this).val(thisVal.replace(/[^0-9]/g,”));
});
});

/*
LastMod: 07.05.2010
*/
function init_fields_v2(){
var res = {};
$("td.ms-formbody").each(function(){
var myMatch = $(this).html().match(/FieldName="(.+)"s+FieldInternalName="(.+)"s+FieldType="(.+)"s+/);
if(myMatch!=null){
// Display name
var disp = myMatch[1];
// FieldInternalName
var fin = myMatch[2];
// FieldType
var type = myMatch[3];
if(type==’SPFieldNote’){
if($(this).find(‘script’).length>0){
type=type+"_HTML";
}
}
if(type==’SPFieldLookup’){
if($(this).find(‘input’).length>0){
type=type+"_Input";
}
}
// Build object
res[fin] = this.parentNode;
res[fin].FieldDispName = disp;
res[fin].FieldType = type;
}
});
return res;
}
</script>
[/javascript]

The parameters “arrToCheckForNum” and “lengthOfInputNumber” must be changed depending on your needs.

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.4.2.min. If you use another version, remember to update the script “src”.

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.