Split Column into Multiple Columns Based Off Specific Criteria

Hi guys, this one is a doozy (atleast for me) and not sure if possible but any help would be appreciated.

I have a worksheet with a column (S) that has multiple pieces of information. For example, it has a general format below:

Street name, Unit/floor/Suite, City, State/Province, Postal Code, Country

Here are a few examples;

252 CENTRAL AVE W BROCKVILLE Ontario K1V 5X1 CAN
126 PITT ST UNIT 1 CORNWALL Ontario K9J 5H3 CAN
2010 YONGE ST FLR 1 TORONTO Ontario M5P 2V8 CAN
C/O PRIME CORE HOLDINGS 25 BANK ST SUITE 31 OTTAWA Ontario K5C 9A1 CAN

As you can see it gets a bit complicated because street names can have varying word counts, and sometimes the unit/floor/suite isnt even displayed. In the last entry of the examples you can see it doesnt even begin with a street name (however, if it gets too complicated you can ignore those since they are rare).

Here is the same examples with how I would ideally want them split.

252 CENTRAL AVE W | BROCKVILLE | Ontario | K1V 5X1 | CAN
126 PITT ST | UNIT 1 | CORNWALL | Ontario | K9J 5H3 | CAN
2010 YONGE ST | FLR 1 | TORONTO | Ontario | M5P 2V8 | CAN
C/O PRIME CORE HOLDINGS | 25 BANK ST | SUITE 31 | OTTAWA | Ontario | K5C 9A1 | CAN

If this is considered to be overly-complicated then you could try a simpler macro (for example, ignore cases of that last example entry). I understand this is hard to do but would be a good challenge so any help would be greatly appreciated. I've looked at other similar "column split" threads but none of them seem to be as complicated in terms of criteria.

Thanks again!

Hi,

as there is no separator within text like a comma for example, it's almost not possible …

Maybe with an attachment I may break data in 5 columns like
Street name & Unit/floor/Suite | City | State/Province | Postal Code | Country
if postal code is always in two words …

Well done jindon !

Same idea to use a regular expression but your pattern is more efficient …