Calculated field to handle strings and numbers.

Question

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?

Answer 1

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.