Text split state and zip into separate columns

[pto160]

I am trying to use Textsplit to put the street address, city, state and zip code into separate columns. I can put the street and city into separate columns, but not the state and zip code.
Is there a way to do this?
The current delimiter is "_x00 a_" and comma.

Book2
	A	B	C	D	E
1	Current
2		Street	City	City/Zip Code
3	20800 Harper oaks dr_x00 a_Yonkers, NY 33806	20800 Harper oaks dr	Yonkers	NY 33806
4	20800 Harper oaks dr_x00 a_Yonkers, NY 33569-5926	20800 Harper oaks dr	Yonkers	NY 33569-5926
5
6
7	What I like	Street	City	State	Zip Code
8	20800 Harper oaks dr_x00 a_Yonkers, NY 33806	20800 Harper oaks dr	Yonkers	NY	33806
9	20800 Harper oaks dr_x00 a_Yonkers, NY 33569-5926	20800 Harper oaks dr	Yonkers	NY	33569-5926
Sheet1

Cell Formulas
Range	Formula
B3:D4	B3	=TRANSPOSE(TEXTSPLIT(A3,,{"_x00 a_",","},,,))
Dynamic array formulas.

 

[hagia_sofia]

Hello, one way to do it could be:

=LET(
r,A3,
a,LEN(r)-LEN(SUBSTITUTE(r," ","")),
b,SUBSTITUTE(r," ","@",a),
TRANSPOSE(TEXTSPLIT(b,,{"_x00 a_",", ","@"},,,)))

 

[pto160]

Thank you so much. That works great.