1

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))),
NULL
)))

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?

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

1 Answer 1

3
IF(
    (ISNUMBER((LEFT(User_Edition__c,FIND(" ", User_Edition__c))))),
    VALUE(LEFT(User_Edition__c,FIND(" ", User_Edition__c))),
    NULL
)

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:

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

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.

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