SPLITBYANY

[lrobbo314]

SPLITBYANY will split a text string by all the characters in the second argument.

The 'chars' argument is passed as an array separated by semicolons, e.g. {", " ; "; "}

=LAMBDA(text,chars,
    TEXTSPLIT(
        INDEX(
            REDUCE(text,
                SEQUENCE(ROWS(chars)),
                LAMBDA(s,c,
                    SUBSTITUTE(s,INDEX(chars,c),"ƛ"))
            )
            ,1
        ),,
        "ƛ"
    )
)

SPLITBYANY
	A	B
1	Input
2	Bill Jelen, bill.jelen@mrexcel.com; Mike Girvin, mike.girvin@excelisfun.com
3
4	Output
5	Name	Email
6	Bill Jelen	bill.jelen@mrexcel.com
7	Mike Girvin	mike.girvin@excelisfun.com
Sheet1

Cell Formulas
Range	Formula
A6:B7	A6	=WRAPROWS(SPLITBYANY(A2,{", ";"; "}),2)
Dynamic array formulas.

 

[Xlambda]

Hi, TEXTSPLIT has native functionality to split by "any" by col and/or by row

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1
2		B J; bj@m.com, M G/ mg@e.com,A B: ab@x.com| C D ; cd@x.com
3
4		=TRIM(TEXTSPLIT(B2,{";",":","/"},{",","|"}))
5		B J	bj@m.com
6		M G	mg@e.com
7		A B	ab@x.com
8		C D	cd@x.com
9
Sheet1

Cell Formulas
Range	Formula
B4	B4	=FORMULATEXT(B5)
B5:C8	B5	=TRIM(TEXTSPLIT(B2,{";",":","/"},{",","|"}))
Dynamic array formulas.

 

[lrobbo314]

Well, I'll be damned. Didn't know you could do that with TEXTSPLIT.