Skip to main content

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")

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.

Reply


Terms & Conditions