locked
numbers RRS feed

  • Question

  • a formula that validates a column only if it Includes any number from 0-9 ,

    or how can i refer to all the numbers from 0 to 9 friom example? in a formula,

    and if some caracters are alowed and can be used in the column but not neceasrly must use them, diference for the rest "not mentioned " that are not alowed.


    sm
    Wednesday, September 14, 2011 12:47 PM

Answers

  • suppose your single line of text column name is cv2, your column validation formula can be:

    OR(ISNUMBER(FIND("0",[cv2])),ISNUMBER(FIND("1",[cv2])),ISNUMBER(FIND("2",[cv2])),ISNUMBER(FIND("3",[cv2])),ISNUMBER(FIND("4",[cv2])),ISNUMBER(FIND("5",[cv2])),ISNUMBER(FIND("6",[cv2])),ISNUMBER(FIND("7",[cv2])),ISNUMBER(FIND("8",[cv2])),ISNUMBER(FIND("9",[cv2])))


    • Edited by GuYuming Thursday, September 15, 2011 9:39 AM
    • Proposed as answer by Reeves Little - Thursday, September 15, 2011 4:45 PM
    • Marked as answer by GuYuming Wednesday, September 21, 2011 8:15 AM
    Thursday, September 15, 2011 9:39 AM
  • Yes, you're correct, that will be valid since it's just checking for the presence of numbers or the + sign.

    Unfortunately formula would need to be very complex in order to restrict the field to only numbers and + signs. You may want to look into using a regular expression instead. It's not easy in 2010 but there are a number of people who have written about doing it. Check this post as a starting point.

    If you really want to use a formula AND you are willing to say you only support a limited number of + signs you could use a combination of: a) replace all plus signs with a number b) validate that the result is a number. It gets very messy and fragile very quickly as you have to decide in advance how many + signs to replace (there is no looping logic in the formula).

    Below is a formula to replace the first  plus sign in the number field. To replace more + signs you'll need to start nesting this formula within itself (example in this thread).

    =REPLACE([Number],(FIND("+",[Number])),1,"0")

    Once you've nested this formula, you'll need to add error detection logic because the formula will fail if you don't have the exact number of + signs you were planning for. Trap those errors with if(iserror) statements to keep the formula resistant to fewer signs than expected. Once you have that working, you can then next it inside the number test formula.

    Cheers,
    Reeves


    -- Program Manager, SharePoint User Experience
    • Marked as answer by GuYuming Thursday, September 22, 2011 3:10 AM
    Wednesday, September 21, 2011 7:13 PM

All replies

  • Hi sm,

    It sounds like you want a field that can have either text or a number, and if it's a number you want to validate it's in a particular range.

    To do this you can use the fact that adding 0 to a value in a SharePoint calculation casts the value as a number. In the formula below I check to see if the adding 0 to the value is equal to the value (which will generate an error if it contains a sting). If it's not an error I the do a validation on the number. I check to see if it's greater than 10, but you can do any validation you like.

    =IF(ISERROR((myColumn+0)=myColumn),1,(IF(myColumn+0>10,0,1)))

    Change the validation in bold to whatever you like, then paste into your columns validation formula.

    Cheers,
    Reeves


    -- Program Manager, SharePoint User Experience
    • Proposed as answer by Ravi S Kulkarni Thursday, September 15, 2011 10:51 AM
    Thursday, September 15, 2011 12:47 AM
  • i was reffering to a validation so i can enter only numnbers that at a point they are breacked whith "+" ex" 000+00000, 00+000 , etc.

    so if i use a number column i did not manadge to validate so it must find "+" in the column ,

    if i used single line of text column i did not manadge to make it accept only number in that column bunt the numnbers does not have to be necesarly used , ex: from 0-9 let say i only use 0,1,2,3,4,5 but not the rest burt i dont know that so i want to make avaliable all the numbers but not obligatory and a caracter for ex "+" and not any lettets

    Thank you


    sm
    Thursday, September 15, 2011 6:00 AM
  • suppose your single line of text column name is cv2, your column validation formula can be:

    OR(ISNUMBER(FIND("0",[cv2])),ISNUMBER(FIND("1",[cv2])),ISNUMBER(FIND("2",[cv2])),ISNUMBER(FIND("3",[cv2])),ISNUMBER(FIND("4",[cv2])),ISNUMBER(FIND("5",[cv2])),ISNUMBER(FIND("6",[cv2])),ISNUMBER(FIND("7",[cv2])),ISNUMBER(FIND("8",[cv2])),ISNUMBER(FIND("9",[cv2])))


    • Edited by GuYuming Thursday, September 15, 2011 9:39 AM
    • Proposed as answer by Reeves Little - Thursday, September 15, 2011 4:45 PM
    • Marked as answer by GuYuming Wednesday, September 21, 2011 8:15 AM
    Thursday, September 15, 2011 9:39 AM
  • ok

    i got something like:

    =IF(OR(ISNUMBER(FIND("0",ss)),ISNUMBER(FIND("1",ss)),ISNUMBER(FIND("2",ss)),ISNUMBER(FIND("3",ss)),ISNUMBER(FIND("4",ss)),ISNUMBER(FIND("5",ss)),ISNUMBER(FIND("6",ss)),ISNUMBER(FIND("7",ss)),ISNUMBER(FIND("8",ss)),ISNUMBER(FIND("9",ss)),ISNUMBER(FIND("+",ss))),TRUE)

    so this validates only if one of the mentioned "" are find but let say if i want n the same column to restrict any other posible caracters or letters? i must do the same whith every one of the caracters and say .. FALSE? or is a simple way to restrict the letters..etc.?

    thank you


    sm
    Thursday, September 15, 2011 10:25 AM
  • sry saiing my column was [ss] not [cv2], but this does not matter
    sm
    Thursday, September 15, 2011 10:26 AM
  • actualy the idea is that the column must only have numbers and possible caracter"+" , but in a Number column cant insert the "+" caracter so i thinked to do it in a text column that is why i was asking hiow to eliminate the letters
    sm
    Thursday, September 15, 2011 10:40 AM
  • The answer provided by GuYuming should work for you, just add one more test for "+" to the formula, making the formula:

    OR(ISNUMBER(FIND("0",[cv2])),ISNUMBER(FIND("1",[cv2])),ISNUMBER(FIND("2",[cv2])),ISNUMBER(FIND("3",[cv2])),ISNUMBER(FIND("4",[cv2])),ISNUMBER(FIND("5",[cv2])),ISNUMBER(FIND("6",[cv2])),ISNUMBER(FIND("7",[cv2])),ISNUMBER(FIND("8",[cv2])),ISNUMBER(FIND("9",[cv2])),ISNUMBER(FIND("+",[cv2])))

    This formula will validate the following way:

    • 45 - Valid
    • 34+56 - Valid
    • Hello23 - Not valid

    Cheers,
    Reeves


    -- Program Manager, SharePoint User Experience
    • Marked as answer by GuYuming Wednesday, September 21, 2011 8:15 AM
    • Unmarked as answer by GuYuming Wednesday, September 21, 2011 8:16 AM
    Thursday, September 15, 2011 4:52 PM
  • Hello23 - Valid!
    sm
    Friday, September 16, 2011 6:16 AM
  • Yes, you're correct, that will be valid since it's just checking for the presence of numbers or the + sign.

    Unfortunately formula would need to be very complex in order to restrict the field to only numbers and + signs. You may want to look into using a regular expression instead. It's not easy in 2010 but there are a number of people who have written about doing it. Check this post as a starting point.

    If you really want to use a formula AND you are willing to say you only support a limited number of + signs you could use a combination of: a) replace all plus signs with a number b) validate that the result is a number. It gets very messy and fragile very quickly as you have to decide in advance how many + signs to replace (there is no looping logic in the formula).

    Below is a formula to replace the first  plus sign in the number field. To replace more + signs you'll need to start nesting this formula within itself (example in this thread).

    =REPLACE([Number],(FIND("+",[Number])),1,"0")

    Once you've nested this formula, you'll need to add error detection logic because the formula will fail if you don't have the exact number of + signs you were planning for. Trap those errors with if(iserror) statements to keep the formula resistant to fewer signs than expected. Once you have that working, you can then next it inside the number test formula.

    Cheers,
    Reeves


    -- Program Manager, SharePoint User Experience
    • Marked as answer by GuYuming Thursday, September 22, 2011 3:10 AM
    Wednesday, September 21, 2011 7:13 PM