|re: '152 HighStreet Colchester South East - Essex CO11PN|
In cases where you don't have spaces, I can think of 2 possible solutions:
1 - Use Substitute to add a space, e.g.
=SUBSTITUTE(A1,"Street"," Street") then use Text to Columns
2 - Use VBA to search the string, character by character for upper case letters and split the cells when it finds one that is not preceded by a space.
Keep in mind that Excel was not designed nor intended to be used as a "text editor". It's happiest when playing with numbers. The designers were nice enough to supply some text-related functions, but it's easy to understand why they are limited in scope.