need forumla for String Splitting

Hi all
I know this can be done, it's just a matter of knowing the right formula. I wrote a But I am unable make 100% correct.

Please help me

Here are the not working scenarios from Sheet2 (col A to col G)

First sheet is test data. Second sheet is my attempt to get what I want out of it.
Row 2 works fine- Column F pulls correct city, column E returns just the street address.
Row 6 doesn't return properly for column E, likely because the data it is using doesn't match for upper/lower case.
Row 9&10, pulls the wrong city for column F- La Palma and Eureka are the street names, but they're also in the city list (column B). This needs to return the LAST instance in the string from D that appears in column B.
Row 11 is bad, too, because it needs to only remove the LAST instance of the content in column F, not ALL instances or first instance only.

Thanks!

Hi sqlinia and welcome to the forum

"we would love to help you, but 1st, according to forum rule 1, please rename your thread to something more meaningful, that actually describes your problem. lots of contributors will look at a thread title, and if it of interest to them, or falls within their area of expertise, and will only open those threads. also, searching for ""help"" will not turn up too many results that will benefit you, sorry

when you name a thread, think ""google"". what kind of returns would you expect if you typed that into google?

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you."