How do I split a string?

I need to split the address from this string. Some strings have 2 delimiters before getting to the address and some have one depending on the number of names. So I started from the right side. I guess there would be a way to start from the left and find the first number also.


ALVES HERMINIA M | 316 CLEVELAND AVE | HARRISON, NJ 07029


BARBOSA MANUEL J AND | MARIA C BARBOSA | 59 LANG STREET | NEWARK, NJ 07105


I was able to get the city, state and zip by using this formula starting from the right side. I don't know how to get the address though. If I could go to the next “|” and subtract the city,st and zip? Or just get the data between the last and second to last “|”.


=TRIM(RIGHT(SUBSTITUTE(TRIM([A!), "|", REPT(" ", 99)), 99))

Sorry, I accidentally posted this twice. Please go to the other post with the same name.