Formula Field help - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Yong LeeYong Lee 

Formula Field help

I want to creat a field which bring certain text from 'Project Name' field.

My Org always use this format of Project name - AB Project - (CD) 2021

I'd like to bring the text in and including parentheses.

e.g.) AB Project - (CD) 2021

(CD) is my target text. It can be one or two usually but sometimes more than two character.

Help me get suitable formula please.
Best Answer chosen by Yong Lee
Eric PraudEric Praud
Apologies, try this instead:
MID( Name,FIND("(",Name),FIND(")",Name)-FIND("(",Name)+1)

 

All Answers

Eric PraudEric Praud
Hi Yong,

Create a formula returning text like so (assuming this field is also on the Project object):
MID(Name,FIND("(",Name),LEN(Name)-FIND(")",Name))

 
Eric PraudEric Praud
Apologies, try this instead:
MID( Name,FIND("(",Name),FIND(")",Name)-FIND("(",Name)+1)

 
This was selected as the best answer
Yong LeeYong Lee
@Eric Praud

Thank you so much Eric!

It is perfectly working!

May I have additional question?

I understand that the - FIND("(",Name) -  means find '(' from Name(field)

But I need to study more to understand the full combinations.

#1. Can you please provide short teaching about this?

#2. If there is no parentheses on project name, can we make it print blank or TBD?

 
Eric PraudEric Praud
Hi,

In answer to your questions:
1- MID takes part of a text field. You need a starting number of character, and the length of character you want to take from the starting number.
the first FIND gives me the starting number as I am finding the first (
The second part FIND(")",Name)-FIND("(",Name)+1 gives me the number of characters to get from the first (

2- Go with this:
IF(NOT(CONTAINS("(", Name)), NULL,
MID( Name,FIND("(",Name),FIND(")",Name)-FIND("(",Name)+1)
)

Change NULL with "TBD" (with the quotation marks) if you want this formula to show TBD instead of nothing
Yong LeeYong Lee
@Eric Praud

Thnak you, this is first time for me go with formula.
Will be fantastic study case for me with your answer detail.

The new formula with null or "TBD" is not working well now, only print empty space or TBD.
Can I get some extra touch please? :) 
Eric PraudEric Praud
Apologies:
IF(NOT(CONTAINS( Name,"(")), NULL,
MID( Name,FIND("(",Name),FIND(")",Name)-FIND("(",Name)+1)
)

 
Yong LeeYong Lee
@Eric Praud

It's percfect now! Thank you so much.
Have nice weekend.
Eric PraudEric Praud
You too Yong!
Yong LeeYong Lee


@Eric Praud, hope you are well and having a goodday.

I want to create a new field that bring text from if all Project Object - Project Indicator field value is same

Project Object has 'Related Engagement' Look up relation field like attached screenshot below.

User-added image

I want the new field brings text value if all Project Indicator field Values are same, if not print some messgae like a 'null' or 'TBD'.....

User-added image

I think I might need Look up relation field with Formula.

But I have no idea how to make it.

Please advise. .

I posted this as a new question. You can reply me from here as well. Thank you

​​​​​​​https://trailblazers.salesforce.com/answers?id=9064S000000DwGr#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Configuration_Data_Management&criteria=OPENQUESTIONS&id=9064S000000DxOWQA0 (https://trailblazers.salesforce.com/answers?id=9064S000000DwGr#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Configuration_Data_Management&criteria=OPENQUESTIONS&id=9064S000000DxOWQA0)