Dec 21, 2021 07:45 PM
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?
Dec 21, 2021 08:02 PM
Your nested SUBSTITUTE()s are incomplete:
SUBSITITUTE(SUBSITITUTE(SUBSITITUTE(SUBSITITUTE(
{Status}, "Floater Membership", "Available"),
"Pending Lease", "Available"),
"Pending Payment","Available"),
"Floater Membership", "Available")
Dec 21, 2021 09:53 PM
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.