I used this formula in conjunction with a series of if(find()) statements. The if(find()) statement searches a multiple select field with 245 for 25 specific options.
IF(FIND(“US”, {Availability}), "US, ", “”) &
IF(FIND(“CA”, {Availability}), "CA, ", “”) &
IF(FIND(“AU”, {Availability}), "AU, ", “”) &
IF(FIND(“NZ”, {Availability}), "NZ, ", “”) &
IF(FIND(“GB”, {Availability}), "GB, ", “”) &
IF(FIND(“IE”, {Availability}), "IE, ", “”) &
IF(FIND(“AS”, {Availability}), "AS, ", “”) &
IF(FIND(“CC”, {Availability}), "CC, ", “”) &
IF(FIND(“CK”, {Availability}), "CK, ", “”) &
IF(FIND(“CX”, {Availability}), "CX, ", “”) &
IF(FIND(“FM”, {Availability}), "FM, ", “”) &
IF(FIND(“GG”, {Availability}), "GG, ", “”) &
IF(FIND(“GU”, {Availability}), "GU, ", “”) &
IF(FIND(“IM”, {Availability}), "IM, ", “”) &
IF(FIND(“JE”, {Availability}), "JE, ", “”) &
IF(FIND(“MH”, {Availability}), "MH, ", “”) &
IF(FIND(“MP”, {Availability}), "MP, ", “”) &
IF(FIND(“NF”, {Availability}), "NF, ", “”) &
IF(FIND(“NU”, {Availability}), "NU, ", “”) &
IF(FIND(“PR”, {Availability}), "PR, ", “”) &
IF(FIND(“PW”, {Availability}), "PW, ", “”) &
IF(FIND(“TK”, {Availability}), "TK, ", “”) &
IF(FIND(“VI”, {Availability}), "VI, ", “”) &
IF(FIND(“IN”, {Availability}), "IN, ", “”) &
IF(FIND(“PH”, {Availability}), "PH, ", “”)
There is then an automation which updates a second multiple select field based on the formula column generated. I’m using a comma at the end of the returned text in the formula in order to separate values, but want to remove the final comma so as not to create the empty value in the resulting multi-select.
I tried added the provided formula as an additional & LEFT({Availability},LEN({Availability})-1).
It did remove the final comma, but it also broke the if/find statements and rather than searching for the 25 specified values it began returning all 245 values. If it was one of the 25 above, it deleted the comma, but if it was one of the other values it returned the selection and deleted the last letter.
Thinking I might have combined it incorrectly with the if/find statements? Any help would be appreciated it.
