Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

Topic Labels: Formulas
308 2
Showing results for 
Search instead for 
Did you mean: 

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:

{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.