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
    Commented Mar 21, 2017 at 18:10
  • Does not solve the problem but thank you for your response. Commented Mar 21, 2017 at 18:12

1 Answer 1

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

  • Thanks Toby, I tried that but get "Error: Incorrect argument type for function 'ISNUMBER()'." I believe ISNUMBER expects Text as an argument. Commented Mar 21, 2017 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
    Commented Mar 21, 2017 at 18:42
  • @JesseMiller Updated my answer
    – gNerb
    Commented Mar 21, 2017 at 18:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .