I have a text field called "User_Edition__c". This field could have a one of three type of values.

  1. It could have a full text string in it like "Unlimited"
  2. It could have a full number like "100".
  3. It could have a partial number, partial text string like "100 Users".

I need a calculated field that pulls out just the number when applicable. So far I have this:

IF((User_Edition__c=='Unlimited'), 10001,
IF((ISNUMBER(User_Edition__c)), VALUE( User_Edition__c ), 
IF((ISNUMBER((LEFT(User_Edition__c,FIND(" ", User_Edition__c))))), VALUE(LEFT(User_Edition__c,FIND(" ", User_Edition__c))),

The first two situations work fine, the third one does not. Im logic is to split on the space in "10 Users" and check if the left side is a number. If it is then return left side as a number. At the moment it fails this and just returns NULL. Any idea why this isn't working?

  • Best practices is to not let it turn into a mess like this. Change it from a text field to a number field to solve your problem. – gNerb Mar 21 '17 at 18:10
  • Does not solve the problem but thank you for your response. – Jesse Miller Mar 21 '17 at 18:12
    (ISNUMBER((LEFT(User_Edition__c,FIND(" ", User_Edition__c))))),
    VALUE(LEFT(User_Edition__c,FIND(" ", User_Edition__c))),

Your ISNUMBER is returning false. Try adding -1 to your find:

LEFT( User_Edition__c, FIND(" ", User_Edition__c)- 1)

I was able to get that to work in my sandbox:

 ISNUMBER(LEFT(Num_Test__c, FIND(" ", Num_Test__c)-1)), 
 VALUE(LEFT(Num_Test__c, FIND(" ", Num_Test__c)-1)),

Note: Note, This obviously doesn't work for different formats. This is also extra work that you probably shouldn't be doing. I'd strongly recommend using a number field instead. You cannot account for every possible way your users will enter data. If the unit of measurement fluctuates (from users to contacts for instance) Create a pick list field that allows users to select the type of measurement. For unlimited, Maybe a checkbox?

Never sacrifice data integrity for end user convenience.

| improve this answer | |
  • Thanks Toby, I tried that but get "Error: Incorrect argument type for function 'ISNUMBER()'." I believe ISNUMBER expects Text as an argument. – Jesse Miller Mar 21 '17 at 18:26
  • yeah, have it as IF( ISNUMBER( VALUE( LEFT(User_Edition__c,FIND(" ", User_Edition__c)))), VALUE(LEFT(User_Edition__c,FIND(" ", User_Edition__c))), NULL ) – o-lexi Mar 21 '17 at 18:42
  • @JesseMiller Updated my answer – gNerb Mar 21 '17 at 18:42

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.