Textsplit loses Delimiter

[pto160]

I am trying to split text by a delimiter, but I want the delimiter to remain. It does not seem to work. I want the text to split text by "CZ" in this example.
Is there a way to do this?

Book3
	A	B
2	CZ DOG ER CZ DOG EE CZ CAT ER CZ CAT EE CZ SNAKE EE CZ SNAKE ER CZ HOUSE EE CZ HOUSE ER
3		DOG ER
4		DOG EE
5		CAT ER
6		CAT EE
7		SNAKE EE
8		SNAKE ER
9		HOUSE EE
10		HOUSE ER
11
12		What I like
13		CZ DOG ER
14		CZ DOG EE
15		CZ CAT ER
16		CZ CAT EE
17		CZ SNAKE EE
18		CZ SNAKE ER
19		CZ HOUSE EE
20		CZ HOUSE ER
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTSPLIT(A2,,"CZ")
Dynamic array formulas.

 

[Cubist]

You can simply concatenate the delimiter:

=TRIM("CZ " & TEXTSPLIT(A2,,"CZ",1))

Another alternative.

=TEXTSPLIT(SUBSTITUTE(A2,"CZ ","|CZ "),,"|",1)

 

[pto160]

Great. Thanks so much. What I am ultimately trying to do is to split text for multiple rows. The second row is "AZ" as the delimiter.
If it helps, the delimiter is the first two letters of the row. Maybe using the left function.

Book3
	A	B	C
1	Text	What I like
2	CZ DOG ER CZ DOG EE CZ CAT ER CZ CAT EE CZ SNAKE EE CZ SNAKE ER CZ HOUSE EE CZ HOUSE ER	CZ DOG ER
3	AZ DOG ER AZ DOG EE AZ CAT ER AZ CAT EE AZ SNAKE EE AZ SNAKE ER AZ HOUSE EE AZ HOUSE ER	CZ DOG EE
4		CZ CAT ER
5		CZ CAT EE
6		CZ SNAKE EE
7		CZ SNAKE ER
8		CZ HOUSE EE
9		CZ HOUSE ER
10		AZ DOG ER
11		AZ DOG EE
12		AZ CAT ER
13		AZ CAT EE
14		AZ SNAKE EE
15		AZ SNAKE ER
16		AZ HOUSE EE
17		AZ HOUSE ER
Sheet2

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTSPLIT(SUBSTITUTE(A2,"CZ ","|CZ "),,"|",1)
Dynamic array formulas.

 

[Cubist]

Are the delimiters always the first characters of the strings i.e. CZ and AZ?

 

[pto160]

Yes, they will be the first two characters. This is a report with states for the first two letters of the row. There could be 10 rows of similar data.

 

[Cubist]

Try:

Book4
	A	B
1
2	Text	What I like
3	CZ DOG ER CZ DOG EE CZ CAT ER CZ CAT EE CZ SNAKE EE CZ SNAKE ER CZ HOUSE EE CZ HOUSE ER	CZ DOG ER
4	AZ DOG ER AZ DOG EE AZ CAT ER AZ CAT EE AZ SNAKE EE AZ SNAKE ER AZ HOUSE EE AZ HOUSE ER	CZ DOG EE
5		CZ CAT ER
6		CZ CAT EE
7		CZ SNAKE EE
8		CZ SNAKE ER
9		CZ HOUSE EE
10		CZ HOUSE ER
11		AZ DOG ER
12		AZ DOG EE
13		AZ CAT ER
14		AZ CAT EE
15		AZ SNAKE EE
16		AZ SNAKE ER
17		AZ HOUSE EE
18		AZ HOUSE ER
Sheet4

Cell Formulas
Range	Formula
B3:B18	B3	=DROP(REDUCE("",A3:A4,LAMBDA(x,y,LET(d,LEFT(y,3),VSTACK(x,d&TEXTSPLIT(y,,d,1))))),1)
Dynamic array formulas.

 

[HongRu]

Try.

=TEXTSPLIT(REDUCE("", A2:A3, LAMBDA(acc,x, acc & SUBSTITUTE(x, LEFT(x, 2), "|" & LEFT(x, 2)))),,"|",TRUE)

 

[pto160]

Absolutely fantastic. Thank you so much to everyone. These are powerful formulas. This will save me so much time. I got to look at learning more about the REDUCE function.

 

[HongRu]

Or try this.

=TEXTSPLIT(TEXTJOIN("",,SUBSTITUTE(A2:A3,LEFT(A2:A3,2),"|" & LEFT(A2:A3,2))),,"|",TRUE)

 

[pto160]

Thanks so much Hong Ru for the last solution. This works great as well.