Print this page

Formula does not return the expected result when referencing a specific value with space from a Picklist field

Knowledge Article Number 000240159
Description When creating a Formula field and referencing the value from a Picklist field, you might notice a different result.

Example: On your Opportunity Stage Picklist field, you have a value which consist of two words and has 3 spaces in between the words.
Tips: Pending[space][space][space]Approval

Tips for Formula:
                 IF( ISPICKVAL( StageName , 'Pending Approval') , 1, 0) 

Result: On an Opportunity record, if Stage is Pending Approval, the formula field will return as 0, even if it should return as 1.
Resolution This can be caused by the extra spaces between words on the Picklist Value. To resolve this:

1) You will need to inspect if the Picklist Value has extra spaces on the open record which has the Pending Approval value for the Stage.
 a) Using Chrome browser you can highlight the picklist value, right click and choose Inspect
 b) On the line <div id="xxxxxxxxxxxxxxxxxxx">Picklist</div> == $0, Highlight or double click on the Picklist Value.

2) This will show if the Picklist value has Extra Spaces
a) You can go directly to the picklist value and click on Edit. You should see if it has spaces or not.  
b) Use the exact value of the Picklist from the Edit Page to ensure that small details like spaces are captured and copied.

When building formulas please always keep in mind special characters, spaces and other potential issues as this can occur in any situation where you're using literal text copied from a different platform.

promote demote