Need help on delimiter

[Harini1100]

Hello Team,

I need to do the segregation for my employees, kindly help me with some formula. Please note the formula should run till end of the last line in the data.

~ is the Key here, but I want the individual employee in rows.

I will use this formula to exclude the additional * =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","Λ"),"*"," "))," ","*"),"Λ"," ")

It would be great that i get single formula to remove extra * and split the employee in rows.

Thanks in Advance...!

 

[etaf]

will ~ always be the first character, if so that can be ignored and not product a row
=TEXTSPLIT(A1,,"~",,1)

Book2
	A	B	C
1	~123~456
2			123
3			456
4
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=TEXTSPLIT(A1,,"~",,1)
Dynamic array formulas.

 

[Fluff]

Another option to do both

=TEXTSPLIT(TEXTJOIN("*",,TEXTSPLIT(A2,"*")),,"~",1)

 

[Jeffrey Mahoney]

Please try this
=TEXTSPLIT(A1,"*","~",TRUE)

I assumed that the asterisk between Jack and Smith was not supposed to be there