seperate coordinate string from multipolygon kml

Dear all,

I have an excel with kml coding (see attached). I would like to convert the multipolygon kml's to seperate polygons. So I need to seperate the coordinate strings between each of the kml codes and write each seperate coordinate string (polygon) into a seperate cell.

As I also want to preserve the ID, name for each polygon, a new row must be created for each seperate coordinate string (polygon) copyging all the imformation into the row. So in case the mukltipolygon has 3 polygons, I must seperate 3 coordinates strings and write them into a new row, attaching the same ID, name, etc to these 3 rows (polygons).

Some multipolygons have 2 polygons, but I also have a few that have up to 8 polygons. Does any of you have an idea how I can do this? In the end I need to have clean and seperated coordinate strings that I can convert to geojson. All it explained in the attached excel. Would be greatefull if someone could help me on this.

I am not a VBA coding expert, so I prefer to solve it with a function, if possible. If VBA is required by default, then it would be great to have a bit of help on that...:-)

Many thanks in advance!

Can you clarify what the sample should look like after processing? i.e. attach manually created end result that you are after.

Since you have Excel 16, I assume that's Excel 2016 for PC.
You should then have access to PowerQuery/Get & Transform.
That will probably be the easiest tool to accomplish your need.

What you need is text processing, something Excel does rather poorly. Since it appears your data is insensitive enough to upload here, it may be insensitive to upload to Google Drive. Which is a roundabout way of suggesting that you use Google Sheets to handle this. If your cells D2 and D3 are representative,

D4: =regexreplace(D2,">[^<].*",">")
D5: =transpose(split(regexextract(D2,"[-,. 0-9]+"),","))

D18: =regexreplace(D3,">[^<].*",">")
D19: =transpose(split(regexextract(D3,"[-,. 0-9]+"),","))

The D4 and D18 formulas extract the initial tags from D2 and D3, respectively, and the D5 and D19 formulas each produce multiple cells with individual coordinates from D2 and D3, respectively.

I don't believe you've provided an exhaustive sample of the strings you're dealing with, this is a text parsing exercise, and Excel simply isn't good at that compared to many other tools. Google Sheets is only a bit better. If you use an advanced text editor like Notepad++, it'd be better still. However, scripting languages with build in support for regular expressions would be the best tools you could use for something like this.

You could do this in VBA, but I believe there are so much better options that I won't pursue that.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.