5

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.

1

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.

| improve this answer | |
  • Thanks for your answer It gave me error when I used formula as follows '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)' and value for the field was DESKTOP:10,LAPTOP:20,MOUSE:20,PHONE:25 – Prafulla Patil Aug 26 '13 at 23:07
  • You'll have to throw in some error checking in your formula depending on the format of the string. And also, you'll have to play around with the formulas to get what you want. See the Update – Scott Pelak Aug 27 '13 at 18:52
  • 2
    As a side note, formulas were not designed to do this sort of work, and one would find oneself running out of "compiled formula size characters" very quickly; I'd guess you'd maybe have 4-5 such formulas before you hit the limit. Use Apex Code instead, which is far better suited to this sort of work. – sfdcfox Aug 27 '13 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.