I have this formula to extract a specific word that starts with a given char “@”. It is working fine, however, there are more words starting with the same, it will only extract the first word.
How to make it extract all?
=TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("@",B2),LEN(B2))," ",REPT(" ",100)),100))
Sample below:
Seq Desc extracted
1 text @word @word
2 text @word_one and @word_two @word_one
Try this formula. it is a slight modification of your formula. What it does is, it searches for the first @ sign than replaces it with ~~ and extracts contained word. then it searches for second @ sign and does the same.
=CONCATENATE(TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",1)),LEN(B3))," ",REPT(" ",100),1),100)),",",TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",2)),LEN(B3))," ",REPT(" ",100),1),100)))
If you would like to add third @ word to extract, just add another instance of CONCATENATE, and replace this IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2(for third word replace value 2 with 3)),LEN(B4)) part of the formula with 3rd occurrence. so for extracting 3 values, please use this formula. (I added IFERROR part just in case word @ is not found)
=CONCATENATE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",1)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",3)),LEN(B4))," ",REPT(" ",100),1),100)),""))
