# Calculated field to handle strings and numbers.

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?

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

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

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

• 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 )` Commented Mar 21, 2017 at 18:42
• @JesseMiller Updated my answer Commented Mar 21, 2017 at 18:42