VBA code for location string split

.
Are you familiar with TEXT TO COLUMNS, located on the DATA TAB ?

When selecting the DELIMITERS you can choose OTHER and type in that symbol ~

@Logit,

I am not sure how great my macro is (after all, you did not click the asterisk/star icon ), but the code seems to me to be quite narrowly focused for something that one would want to add to one's toolbox.

Hi Ricky,

Thank you so much for your reply!

I ran the macro and it works almost fine as some of the postcodes are still populated in the field of the "City" instead.

Would there be a way to correct this? Also, how would I make the split more proper; i.e. populating the whole string (of street address as well as "city, region, country" only) to relevant fields?

Attached please find the updated sheet including the application of the above code for better illustration.

Would there be any way to cater for all variances of the Address structure?

Many thanks.

.
OK Rick ... I've stroked your ego by adding a "Thank You".

I still think it is good work nevertheless ...


Stay healthy my friends !

Hi Jindon,

Once again, thank you so much...

I have tried to use the above code, however, since the input is free text the code eventually breaks due to mismatch of structure as defined by regx..

Could you please let me know a simple way to split the free text (location including street not starting with "~") in a separate column (street)? this input would only be used to determine the country (which is mandatory) while the rest would be put in one column (i.e. "street"). The rest should be split as per above; i.e. country, region, city.

Many thanks,