+ Start a Discussion
knightknight 

question on formula fields

I wanted to create formula field to which will retrieve values from a picklist. Here is the catch...some of the picklist values do have a "&" on it. But the formula field shouldnt bring in these values..instead it should replace it with "and" ..Is this possible ?

Best Answer chosen by Admin (Salesforce Developers) 
Steve MolisSteve Molis

You could try using a SUBSTITUTE function in your Formula

 

SUBSTITUTE

Description:Substitutes new text for old text in a text string.
Use:SUBSTITUTE(textold_textnew_text) and replace text with the field or value for which you want to substitute values, old_text with the text you want replaced, and new_text with the text you want to replace the old_text.
Example:SUBSTITUTE(Name, "Coupon", "Discount")returns the name of an opportunity that contains the term “Coupon” with the opportunity name plus “Discount” wherever the term “Coupon” existed.

SUBSTITUTE(Email, LEFT(Email, FIND("@", Email)), "www.") finds the location of the @ sign in a person's email address to determine the length of text to replace with a “www.” as a means of deriving their website address.

Tips:
  • Each term provided in quotes is case sensitive.
  • If the old_text appears more than once, each occurrence is replaced with the new_text

All Answers

Steve MolisSteve Molis

You could try using a SUBSTITUTE function in your Formula

 

SUBSTITUTE

Description:Substitutes new text for old text in a text string.
Use:SUBSTITUTE(textold_textnew_text) and replace text with the field or value for which you want to substitute values, old_text with the text you want replaced, and new_text with the text you want to replace the old_text.
Example:SUBSTITUTE(Name, "Coupon", "Discount")returns the name of an opportunity that contains the term “Coupon” with the opportunity name plus “Discount” wherever the term “Coupon” existed.

SUBSTITUTE(Email, LEFT(Email, FIND("@", Email)), "www.") finds the location of the @ sign in a person's email address to determine the length of text to replace with a “www.” as a means of deriving their website address.

Tips:
  • Each term provided in quotes is case sensitive.
  • If the old_text appears more than once, each occurrence is replaced with the new_text
This was selected as the best answer
knightknight

thanks Steve...that worked

Steve MolisSteve Molis

No problem, you owe me a beer! 

http://beeradvocate.com/beer/profile/863/7971