+ Reply to Thread
Results 1 to 6 of 6

seperate coordinate string from multipolygon kml

  1. #1
    Registered User
    Join Date
    01-26-2020
    Location
    Amsterdam
    MS-Off Ver
    16
    Posts
    17

    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!
    Last edited by Kievet; 02-13-2020 at 04:26 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: seperate coordinate string from multipolygon kml

    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.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: seperate coordinate string from multipolygon kml

    Not sure if I 100% understand your request but will try

    Column Q: ID in Column A, QTY of ID = QTY of Column F:M (for same ID) with content (not blank)
    Column R: "Polygon String" from Column F:M (if not blank) per sequence of ID in column Q

    Q2=IF(ROW(A1)=1,A2,IFERROR(IF(COUNTIF($Q$1:Q1,Q1)+1>COUNTIF(OFFSET($A$1,MATCH(Q1,$A:$A,)-1,5,1,8),"<>"),INDEX(A:A,MATCH(Q1,A:A,)+1),Q1),"")) copydown

    R2=IF($Q2>0,VLOOKUP(Q2,$A:$M,5+COUNTIF($Q$2:Q2,Q2),),"") copydown

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,473

    Re: seperate coordinate string from multipolygon kml

    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.
    Last edited by hrlngrv; 02-13-2020 at 05:42 PM.

  5. #5
    Registered User
    Join Date
    01-26-2020
    Location
    Amsterdam
    MS-Off Ver
    16
    Posts
    17

    Re: seperate coordinate string from multipolygon kml

    Thanks for the comments and proposed solutions! I saw there was an error in my excel (appologies), but I am sure I can use your solutions. Very much appreciated!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: seperate coordinate string from multipolygon kml

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Length and distance from the line coordinate and point coordinate
    By sanju2323 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2019, 03:13 PM
  2. [SOLVED] Formula to Seperate streetname from street+Postcode string
    By FredFitzgerald in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-19-2016, 08:21 AM
  3. Seperate a string into Rows / Columns based on a symbol
    By tweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-14-2016, 06:55 PM
  4. Seperate Text from String
    By krunk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2014, 10:37 AM
  5. How can I seperate numbers followed by a comma from a string
    By jaymullasseril in forum Excel General
    Replies: 2
    Last Post: 07-30-2011, 01:36 AM
  6. return value of X coordinate and Y coordinate?
    By sbmoller in forum Excel General
    Replies: 1
    Last Post: 09-22-2007, 06:54 AM
  7. Seperate cell string into individual cells
    By ERudy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2006, 02:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1