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

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?

Your nested SUBSTITUTE()s are incomplete:

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

Welcome to the community, @Business_Development! :smiley: 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.

5 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.