Google Sheets – How to Split into Specific Delimiter Columns

google sheets

I have a column with multiple delimiters, but not all cells have all delimiters
i.e.

delimiter *@#
*text1@text2#text3
*text1#text3

I there a way to SPLIT into

col1   col2   col3
text1  text2  text3
text1         text3

Best Answer

This should work for the examples you have provided: 

=if(and(left(A1)="*",countif(A1,"*#*")=1,countif(A1,"*@*")=0),split(SUBSTITUTE(A1,"#","* *"),"*"),split(substitute(substitute(A1,"@","*"),"#","*"),"*"))

but with this approach the formula would be a lot longer (nesting many more similar IF statements) if to cover what it seems may be other possibilities.

Related Topic