I have this sheet that consist on a two step formula
First I have a search formula to find different words (allergens) 26 to be precise on a cell that contains ingredient information. Once all allergens are identified they are combines with the formula below. However I get a bunch of double commas or comas at the end where they are not needed anymore
This is the formula that combines only 15 cells and I need 26 to not give me any extra comas or comas at the end
="CONTAINS: "&IF(AND(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),1)=",",RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),1)=","),MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","))-2),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),1)=",",RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","))-1),IF(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),1)=",",LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CONCATENATE(AF208,",",AG208,",",AH208,",",AI208,",",AJ208,",",AK208,",",AL208,",",AM208,",",AN208,",",AO208,",",AP208,",",AQ208,",",AR208,",",AT208)),",,",","),",,",","),",,",","))))
CONTAINS: MILK, SOY, EGG, WHEAT,
Bookmarks