Macro to split text string based on its various structure/delimiters

Hi guys,

THIS THREAD WAS PREVIOUSLY WRONGLY MARKED AS DUPLICATE, BUT I AM STILL LOOKING FOR A SOLUTION TO THE BELOW AS THE REQUIREMENTS DIFFER FROM THE PREVIOUS QUESTION - THANKS IN ADVANCE.

I am looking for Macro, which would separate strings based on delimiter as the previous version always breaks when I am trying to consume larger amounts of data – as these turned out to have uneven structure (i.e. the user has an option to put free text), which reflects as delimited by “;” without “~” – please see the example of “free text” in the sheet2.

If the input within the string starts with “~” then it means that the string has 1- to three elements – COUNTRY, CITY, REGION – these should all be populated in relevant columns of the Sheet2 and if any of them is missing it will simply reflect as an empty space. The country is always separated by ~,~ and is always present – even when the input is done by free text.
For clear illustration please see the attached example.


Thanks a million!

This one is ignoring multiple duplicate thread warning from the Moderators 3, 4 times already regarding this same question.

This is almost like a spam to me...