VBA split according to two distinct rules

[Salasnik]

Hi guys,

I would like to ask for advice that would help me to get TWO options very similar codes which do the following:
1) Code (perhaps do until loop?), which splits the data from column “S” of the sheet “Address” and populates these unique 1-5 string location in between the column J and N of the sheet “destination”, where each string of the 1-5 string address is being placed into separate (relevant) column, while each unique address sits on the separate line. As implied, the code should run until it inspects all the strings within the relevant row of the cell S within “Address” sheet. The example of the possible structure of 3-5 string locations can be found attached (sheet “address”) along with how these should be populated (sheet “destination”).

2) Code which does the same as above, BUT treats the string containing street details as “as unstructured” placing this type of input in one column – i.e. disregarding the need for a split due to variability of the format and lacking pattern. The “classic” 1-3-string input (i.e. country, region, city OR city, country OR country only) shall still be populated into relevant cells. Please see the example in “destination 2” tab.


Please note the “location country should always be populated (i.e. in scenario 1 as well as 2) as this is a mandatory/structured input (always sits at the end of the string). Also, please note the attached is just sample and the code should be dynamic (i.e. applicable to bigger data samples)

Attached please see the examples for better illustration.

Kind regards,

Salasnik

[Kaper]

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

The administrator will close this thread soon, but you may continue here in the original thread: https://www.excelforum.com/excel-pro...lp-needed.html