How can I extract text before a specific character that can occur multiple times in different places within a single cell

Question

I'm trying to find a single formula(preferable, Excel 2016)/VBA solution that would work in my case. Basically I have a column where every cell contains different text that looks like this

google (@), microsoft (#), hewlett and packard (@), tesla ($), random text i run out of names (@), another random text (%)

where x is different text. I need to extract all values that occur before (@) and (@) can occur up to 3 times within the same cell placed randomly (beginiing, middle, end). the output should look like

google, hewlett and packard, random text i run out of names

what would be the best solution? Thank you!

tried this solution

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

changing LEFT to RIGHT, but failed - it doesn't pick up the names to the right of (@)

Answer 1

You can use FILTERXML:

=SUBSTITUTE(FILTERXML("<t><s>" & SUBSTITUTE(A1,",","</s><s>") & "</s></t>","//s[contains(.,'(@)')]"),"(@)","")

If the results do not SPILL down (I cannot recall if 2016 has that feature, then you can return the individual results by adding the INDEX function and filling down the formula until you get blanks:

=IFERROR(INDEX(SUBSTITUTE(FILTERXML("<t><s>" & SUBSTITUTE($A$1,",","</s><s>") & "</s></t>","//s[contains(.,'(@)')]"),"(@)",""),ROWS($1:1)),"")

Results

If you really want it in a comma-separated string, and not in separate cells, then you can use, in earlier versions:

=TRIM(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE($A$1,",","</s><s>")&"</s></t>","//s[contains(.,'(@)')][1]"),"(@)",""))
& IFERROR(", " &  TRIM(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE($A$1,",","</s><s>")&"</s></t>","//s[contains(.,'(@)')][2]"),"(@)","")),"")
& IFERROR(", " &  TRIM(SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE($A$1,",","</s><s>")&"</s></t>","//s[contains(.,'(@)')][3]"),"(@)","")),"")

or, as pointed out by @joswoolley in the comments below, you could use CONCAT function which I did not realize was available in Excel 2016:

=REPLACE(CONCAT(", "&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[contains(.,'(@)')]")," (@)","")),1,2,"")

Results