Help

Re: Is it possible to combine formulas to avoid having multiple formula columns

986 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Business_Develo
5 - Automation Enthusiast
5 - Automation Enthusiast

I have 4 sets of formulas.

SUBSTITUTE({Status}, “Floater Membership”, “Available”)
SUBSTITUTE({Status}, “Pending Lease”, “Available”)
SUBSTITUTE({Status},“Pending Payment”,“Available”)
SUBSTITUTE({Status}, “Floater Membership”, “Available”)

We wanted to merge them into one. I tried to do it with this formula SUBSTITUTE(SUBSTITUTE({Status}, “Pending Transfer”, “Available”), “Pending Lease”, “Available”), “Pending Payment”, “Available”), “Floater Membership”, “Available”) but it is not working. What is your workaround with this?

2 Replies 2

Your nested SUBSTITUTE()s are incomplete:

SUBSITITUTE(SUBSITITUTE(SUBSITITUTE(SUBSITITUTE(
{Status}, "Floater Membership", "Available"),
"Pending Lease", "Available"),
"Pending Payment","Available"),
"Floater Membership", "Available")

Welcome to the community, @Business_Development! :grinning_face_with_big_eyes: This could also be done by using a regular expression. Putting all of the options into a single string separated by vertical bars tells the REGEX interpreter to replace any of those options.

REGEX_REPLACE(Status, "Pending Transfer|Pending Payment|Pending Lease|Floater Membership", "Available")

I also noticed that “Floater Membership” was listed twice in your original, but you have “Pending Transfer” in your conversion attempt, so I used that for one of the duplicates.