How to extract values using Salesforce Formula fields

I have one Text field on Custom Object as follows –

Product_Info__c = ‘DESKTOP:10,LAPTOP:20,MOUSE:20,PHONE:25’ //sample value

I want to create new formula field as DesktopCount__c which will result value as 10 in above case.

DesktopCount = 10

LAPTOPCount = 10 .. etc

Note: I can do this apex but wanted to check if its possible to do using formulas.

Answer

Yea, use the FIND method coupled with an IF or two, and you should be on your way.

Example: (decimal) DesktopCount =

IF(CONTAINS(Product_Info__c,'DESKTOP:',VALUE(MID(Product_Info__c,FIND('DESKTOP:',Product_Info__c),FIND(',',Product_Info__c,FIND('DESKTOP:',Product_Info__c)))),0)

If the prefix text is there, grab the MID text between the prefix-text and its delimiter.

Update

I didn’t test my function, but the basic idea is there. I want my formula to do the following:

(1)  Check if the "prefix-text" is there.  
       Example: is "Desktop:" in my string?
       Available Functions:  CONTAINS()

(2)  If yes, take the text between the prefix-text and the delimiter
       Example: prefix-text="Desktop:"; delimiter=",";  result = "10"
       Available Functions:  MID(), FIND()

(3)  If there is a result, convert it to a number
       Example: 10
       Available Functions: VALUE()

Here’s a list of Formula Functions you can use.

Attribution
Source : Link , Question Author : Prafulla Patil , Answer Author : Scott Pelak

Leave a Comment