This is a very complex thing for which I seldom see good solutions for. The reason is because the data seldoms follows any sort of "rules" or "patterns" (at least not consistently!).
The thing to understand about programming is it is not "magic". On the contrary, it is very literal. It does only exactly what you tell it to (it cannot think on its own).
For problems like these, see if you can come up with some "logic" or "rules" (in plain English, not in programming), to instruct what should happen in different scenarios.
If you cannot come up with those "rules" in plain English (non-technical), then you cannot program (it would be like trying to build a house without a blueprint).
For example, you could tell it to "split it before the first number", and that would work in many cases, but would only be correct for some of the options.
You could add more rules, to try to identify different situations, but inevitably you will always have exceptions.
The best I have seem people do is come up with some rules that might work correctly on 80 - 90% of their data, but they still need to go through each row and make the corrections where necessary.
If you can come up with some "rules" (in plain English) for us to program to, we can probably do that for you.
But I don't think you are going to find a handful of rules that will handle the vast majority of your records correctly.
You can only do so much with "bad/unformatted" data.
