function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
New_DeveloperNew_Developer 

Help with formula field

Hi 

I need help with formula field. Right now i have a fields called "Mailing Name" and i have to bring a part of text from that field into a formula field.

 

Ex:

Mailing Name : India CO (243)

 

I just want the number in the brackets in the formula field. I tried to create one like the below

MID(Mailing_Name__c, FIND("(", Mailing_Name__c), FIND(")", Mailing_Name__c)) but the output for the formula field ia (243) and i just want the number not the brackets.

 

Is there any other way of creating this formula field where i can just pull the number in the brackets of the Mailing Name field.

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
phiberoptikphiberoptik
LEFT(MID(MID(Mailing_Name__c, FIND("(", Mailing_Name__c), FIND(")", Mailing_Name__c)),2,6),LEN(MID(MID(Mailing_Name__c, FIND("(", Mailing_Name__c), FIND(")", Mailing Name__c)),2,6))-1)

 

As long as the number of characters between the () are no more than 5, this will work. If it could ever become 6, then you need to change the 6s in the formula to 7s.

All Answers

phiberoptikphiberoptik

Does the Mailing Name field always end in (###)?

 

LEFT(RIGHT(Mailing_Name__c,4),3)

 

This should give you only the number if  the field always ends in (###).

New_DeveloperNew_Developer

Thanks. The mailing name fields has a number which starts and ends in brackets but the number will not be always 3 digit length. It can be even 4 or 5 digit number or sometimes 2 digit too. You formula fields pulls the correct number if the number in the brackets is 3 digits. 

 

Thanks

phiberoptikphiberoptik
LEFT(MID(MID(Mailing_Name__c, FIND("(", Mailing_Name__c), FIND(")", Mailing_Name__c)),2,6),LEN(MID(MID(Mailing_Name__c, FIND("(", Mailing_Name__c), FIND(")", Mailing Name__c)),2,6))-1)

 

As long as the number of characters between the () are no more than 5, this will work. If it could ever become 6, then you need to change the 6s in the formula to 7s.

This was selected as the best answer
New_DeveloperNew_Developer

Thanks for your reply.

 

It worked perfect. Can i just give 10 instead of 6 just to be on the safe side.

phiberoptikphiberoptik
Yes. That number is arbitrary, so long as it is at least 1 greater than the highest potential number of characters between the parentheses.
New_DeveloperNew_Developer

Thankyou so much.