Longitude/Latitude wrongly read from XML

**lonnebol** · 12-16-2021, 08:29 AM

I cannot seem to figure this out.
I have an Excel sheet to calculate automatically the driving distance between to addresses. I do this using the Bing Maps API. A URL is filled in with the address and this will give back another URL which is then converted to XML. I then pull the longitude and latitude out of this XML using FILTERXML. In a similar way I can get an XML with the driving distance and driving time between two addresses. And this is where it goes wrong. Somehow, when the distance is small, the distance is displayed correctly, for example 3.91 km. But when the number of decimals is larger than 2, Excel 'converts' for example 67.698 to 67698. See the attached Excel file and a screenshot on how the data is displayed right now.

I tried by changing the format (text, custom, etc.), but that does not work.

Similar the reading/conversion of latitude and longitude is not working properly, but there I found a way to solve that, but it is not a beautiful solution in my opinion.

How can I make Excel show the data it reads from an XML correctly?

**AliGW** · 12-16-2021, 08:57 AM

Welcome to the forum.

You've not told us the formula you are using to calculate the distances. Please add this to your sample workbook and post again. Thanks.

**AliGW** · 12-16-2021, 09:03 AM

Try this:

=--TRIM(LEFT(SUBSTITUTE(MID(T3,FIND("TravelDistance",T3)+15,10),"<",REPT(" ",99)),99))

**lonnebol** · 12-16-2021, 09:26 AM

Originally Posted by AliGW

Welcome to the forum.

You've not told us the formula you are using to calculate the distances. Please add this to your sample workbook and post again. Thanks.

Thank you!

I do not calculate the distance. This is done online by giving in the locations (latitude and longitude). The URL gives back the XML with the data. I can't post the formula for it in the Excel, because of a personal API key. Sorry for that.

Originally Posted by AliGW

Try this:

=--TRIM(LEFT(SUBSTITUTE(MID(T3,FIND("TravelDistance",T3)+15,10),"<",REPT(" ",99)),99))

I tried to add it to my sheet, but it gives me #VALUE on the two decimal values and the other values are shown like before (e.g. 67698 instead of 67.698). Same thing happens when I paste other data into your workbook.

What is really happening here? Why is Excel not giving me back the value as it is in the XML?

**Haluk** · 12-16-2021, 09:47 AM

I guess your API Key is in the URL of the Rest API. So, may be you need to edit your first message and remove the attached file.

On the other hand, the related data can be directly fetched by sending a request to the server by VBA.

**Fluff13** · 12-16-2021, 09:55 AM

As you haven't shown us the filterxml formula you are using it's difficult to tell as

Formula:

Please Login or Register  to view this content.

It maybe that your system uses the . for the thousand separator, rather than the decimal point.

**lonnebol** · 12-16-2021, 10:24 AM

Originally Posted by Haluk

On the other hand, the related data can be directly fetched by sending a request to the server by VBA.

I was hoping to avoid VBA. I know how to use it, but preferably not.

Originally Posted by Fluff13

As you haven't shown us the filterxml formula you are using it's difficult to tell as

Formula:

Please Login or Register  to view this content.

It maybe that your system uses the . for the thousand separator, rather than the decimal point.

That is exactly the formula I am using. See the attached file (updated).

Is there a way to change what separator is used without setting it on the system? The file should work on every computer.

**AliGW** · 12-16-2021, 10:33 AM

My formula works. Maybe you made a mistake copying doen? Why not provide evidence of it not working (NOT a picture, but a workbook)?

**lonnebol** · 12-16-2021, 10:37 AM

Originally Posted by AliGW

My formula works. Maybe you made a mistake copying doen? Why not provide evidence of it not working (NOT a picture, but a workbook)?

You are right. See the attachment.

**AliGW** · 12-16-2021, 10:44 AM

I don't know what you are doing, but it's working fine here.

If you are copying and pasting, try this:

=--SPATIES.WISSEN(LINKS(SUBSTITUEREN(DEEL(T3;VIND.ALLES("TravelDistance";T3)+15;10);"<";HERHALING(" ";99));99))

**Fluff13** · 12-16-2021, 10:47 AM

As long as all user use the comma as the decimal separator you could use this tweak to Ali's formula

Formula:

Please Login or Register  to view this content.

**Haluk** · 12-16-2021, 10:49 AM

Can you try Ali's formula, which is edited as below;

Please Login or Register  to view this content.

**DJunqueira** · 12-16-2021, 11:02 AM

In T4 if I use the formula =FILTERXML(T4,"//TravelDistance") I will get 1.77 as text, my decimal separator is comma, if I place an extra zero in the XML text in T4 the formula will give me 1770 as number, as pointed by Fluff13.

**lonnebol** · 12-16-2021, 11:05 AM

Originally Posted by Haluk

Can you try Ali's formula, which is edited as below;

Please Login or Register  to view this content.

Yes, that did the trick! Thank you all very much for your help.

**DJunqueira** · 12-16-2021, 11:06 AM

Originally Posted by Haluk

Can you try Ali's formula, which is edited as below;

Please Login or Register  to view this content.

Using Ali's formula in T4 I got 1,77 as number to my comma decimal separator system, the correct answer.

**AliGW** · 12-16-2021, 11:33 AM

Originally Posted by lonnebol

Yes, that did the trick! Thank you all very much for your help.

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Longitude/Latitude wrongly read from XML

LinkBack

Thread Tools

Rate This Thread

Display

Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Re: Longitude/Latitude wrongly read from XML

Thread Information

Users Browsing this Thread

Similar Threads

Sort by Latitude and Longitude

Latitude/longitude computation?

Distance between latitude and longitude

Latitude & Longitude processing help please

MGRS to Longitude/Latitude

[SOLVED] formula for longitude/latitude

[SOLVED] Help with Latitude and Longitude.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions