Dear members, I am new to this forum. I have posted this one also on the general excel thread, but maybe this was wrong because I realised my question is related to functions. Apologies if I am creating a problem if I post this same question also the this thread. Member BMV also helped me a bit in the other thread.
I have with a large dataset (> 8000 rows), which needs to be modified. In this dataset, I have kml coding (Google Earth) that needs to be converted to geojson code. I have come pretty far so far, but this problem gives me a headache:
The kml format has 3 sectors for each coordinate seperated by comma's: lon,lat,accuracy. So it looks like this: 44.980061,-16.200435,20.32. Here, accuracy is 20.32. The accuracy can have a different number of digits for example 20,32 or 0.0. For example: 45.036191,-16.217396,0.0 or 44.980061,-16.200435,20.32. I have a very long string of these kml codes in each cell (to create polygons). All lon,lat,accuracy coordinates are seperated by a space (" ") so the format is like this: 44.980061,-16.200435,20.32 45.036191,-16.217396,0.0, etc, etc. Now the problem: For the geojson, I need to remove the accuracy string. So, I basically need: 44.980061,-16.200435 45.036191,-16.217396 etc, etc. In the attached excel, I give a few examples.
My question is, how can I get rid of those accuracy digits? The difficulty is that the accuracy string can have variable lengths. My first thought is:
1) Search for the spaces (" ") between two coordinates
2) Search back (to left) to find the first comma in the coordinate
3) Count the number of digits between the space and first comma
4) Remove those digits and replace it by a space (" ")
I tried working with SEARCH, SUBTITUDE, LEFT/MID etc, but I can't find the right code to make it work. Any help is very much appreciated! Thanks.
Bookmarks