Thread: Excel Mapping Coordinates Replacement

Hey all,

I've got an excel issue I'd like some help with. I've been mapping out some lakes and their respective coordinates with google earth. Sometimes I need to make new coordinates because their old coordinates were wrong--I need to change a LOT of stuff and need an easier way to do so. Regardless of the context, I have a situation where I have this:

Column A - Lake Name
Column B - Longitude of Lake
Column C - Latitude of Lake
Column D - Really long xml code found below. I need the bolded part below to replace B & C above.


"<?xml version=""1.0"" encoding=""UTF-8""?>
<kml xmlns=""http://www.opengis.net/kml/2.2"" xmlns:gx=""http://www.google.com/kml/ext/2.2"" xmlns:kml=""http://www.opengis.net/kml/2.2"" xmlns:atom=""http://www.w3.org/2005/Atom"">
<Document>
<name>KmlFile</name>
<Style id=""sn_ylw-pushpin"">
<IconStyle>
<scale>1.1</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
<hotSpot x=""20"" y=""2"" xunits=""pixels"" yunits=""pixels""/>
</IconStyle>
</Style>
<StyleMap id=""msn_ylw-pushpin"">
<Pair>
<key>normal</key>
<styleUrl>#sn_ylw-pushpin</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_ylw-pushpin</styleUrl>
</Pair>
</StyleMap>
<Style id=""sh_ylw-pushpin"">
<IconStyle>
<scale>1.3</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
<hotSpot x=""20"" y=""2"" xunits=""pixels"" yunits=""pixels""/>
</IconStyle>
</Style>
<Placemark>
<name>Untitled Placemark</name>
<LookAt>
<longitude>-71.62791402659985</longitude>
<latitude>41.69594132714693</latitude>
<altitude>0</altitude>
<heading>0.07945165186458728</heading>
<tilt>0</tilt>
<range>7452.521349966087</range>
<altitudeMode>relativeToGround</altitudeMode>
<gx:altitudeMode>relativeToSeaFloor</gx:altitudeMode>
</LookAt>
<styleUrl>#msn_ylw-pushpin</styleUrl>
<Point>
<altitudeMode>absolute</altitudeMode>
<coordinates>-71.60704512966066,41.69591783028302, 125.114287591884</coordinates>
</Point>
</Placemark>
</Document>
</kml>
"

Does anybody have an idea as to the function that will automatically replace B & C with the two coordinates found bolded in the google earth xml code?

Thanks

Just to clarify, will that long piece of XML code be in cell D2 for a lake in A2, and then you will have a similar entry in D3 for another lake in A3, and so on ?

Can I also ask why the <longitude> and <latitude> parameters a few rows above are not the same as the <coordinates> row.

Pete

Pete,

Yes, the long piece of code is in D2 for only A2. And Yes, there will be similar effects for A3, B3, C3, D3.

Regarding the coordinates--I wish I knew that myself! When I plot things on the map it is weird, but the coordinates are what I want and the long, lat are different. If you notice, the numbers are different in the code above...not sure why.

When I try to copy/paste your example XML code into D2 only, it ends up going into separate cells D2:D54, so I can't reproduce what you have. Can you post an example file, with say 5 rows of data so I can test formulae out against it ?

Pete

Ah, right, I managed to do it by pasting into the formula bar.

If your XML code is all very similar to what you posted, then you can apply Find and Replace twice to reduce most of it to more reasonable data. Highlight column D by clicking on the column identifying letter, and then do CTRL-H:

Find what: *<coordinates>
Replace with: leave blank
Click Replace All
CTRL-H again
Find what: </coordinates>*
Replace with: leave blank
Click Replace All

This will remove all the text apart from (taking your example data above):

-71.60704512966066,41.69591783028302, 125.114287591884

Notice that the second comma has a space after it, whereas the first comma does not - I'm assuming that all your data is like this, and if it is you can then put this formula in B2:

=LEFT(D2,SEARCH(",",D2)-1)

and this one in C2:

=MID(D2,SEARCH(",",D2)+1,SEARCH(", ",D2)-SEARCH(",",D2)-1)

in order to extract the first two values. However, you will proabably want them as numeric values, in which case you just need to put a double minus in front of the formula, i.e.:

B2: =--LEFT(D2,SEARCH(",",D2)-1)
C2: =--MID(D2,SEARCH(",",D2)+1,SEARCH(", ",D2)-SEARCH(",",D2)-1)

These can then be copied down as far as you need to, and then you can fix the values and delete column D.

Hope this helps.

Pete

Regarding the commas, there should be no spaces. I'm not sure how that one got in there.

Well, I can only work with the data that you have given me. Can you post an example file, as requested earlier, so I can see exactly what you have?

Pete

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Document>
<name>KmlFile</name>
<Style id="sn_ylw-pushpin">
<IconStyle>
<scale>1.1</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
<hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/>
</IconStyle>
</Style>
<StyleMap id="msn_ylw-pushpin">
<Pair>
<key>normal</key>
<styleUrl>#sn_ylw-pushpin</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_ylw-pushpin</styleUrl>
</Pair>
</StyleMap>
<Style id="sh_ylw-pushpin">
<IconStyle>
<scale>1.3</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>
</Icon>
<hotSpot x="20" y="2" xunits="pixels" yunits="pixels"/>
</IconStyle>
</Style>
<Placemark>
<name>Untitled Placemark</name>
<LookAt>
<longitude>-71.62791402659985</longitude>
<latitude>41.69594132714693</latitude>
<altitude>0</altitude>
<heading>0.07945165186458728</heading>
<tilt>0</tilt>
<range>7452.521349966087</range>
<altitudeMode>relativeToGround</altitudeMode>
<gx:altitudeMode>relativeToSeaFloor</gx:altitudeMode>
</LookAt>
<styleUrl>#msn_ylw-pushpin</styleUrl>
<Point>
<altitudeMode>absolute</altitudeMode>
<coordinates>-71.60704512966066,41.69591783028302,125.114287591884</coordinates>
</Point>
</Placemark>
</Document>
</kml>

I think I may have made a mistake earlier when bolding the coordinates. Maybe that is why the space was there. Sorry!

example.xlsx

Hopefully this example attachment helps.

Okay, I'm not sure if this is a one-off for you, or if you have several such files, but here's the procedure:

Insert 2 new columns I and J, pushing the XML data across into column K.
Put these formulae in the cells stated:

J2: =REPLACE(K2,1,SEARCH("<coordinates>",K2)+12,"")
I2: =--LEFT(J2,SEARCH(",",J2)-1)
H2: =--MID(J2,SEARCH(",",J2)+1,FIND(CHAR(1),(SUBSTITUTE(J2,",",CHAR(1),2)))-1-SEARCH(",",J2))

Copy these down as far as you need to. Then fix the values and delete columns J and K. You can then copy H and I to overwrite what you have in C and D.

The attached workbook shows this in action, as it were.

Hope this helps.

Pete

This sounds like it will work. I'll let you know if I need anything else. I greatly appreciate the help Pete.

You're welcome. Don't forget to come back and mark the thread as Solved if you think it has been. Also, to pass on thanks, you can click on the "star" icon at the bottom of any post that has helped you.

Pete