Welcome to the Board!
Can you explain, in plain English, that for any entry, how to determine where the street name ends and the city name starts?
If you cannot explain the logic to use that will work every time, then you probably cannot program it.
Programming in Excel is not magic, it is very literal. You have to first come up with the rules to follow, and then you can program to those.
However, if you cannot come up with well-defined "rules" for us to follow that will work or every entry you have, then we probably cannot build you anything that is guaranteed to always work.
This question gets asked a lot with splitting things like addresses and names. Unfortunately, it can be very difficult to work with unformatted (or poorly formatted) data.
Usually, the best that you can do is to try to come up with a rule that works "most of the time", but then that still leaves you with having to go through and manually check and fix entries.
