1

Somebody gave me an Excel table and the latitude and longitude had the wrong place for decimals. Is there a workaround to fix the problem? I am pretty much stuck trying to figure it out. Here is the example of what I am talking about:

enter image description here

The latitude and longitude should be like this:

3854.6 = 38.546, -10612.68 = -106.1268

  • 4
    And you don't divide by 100 because... – Vince May 22 at 23:14
  • That works ...I use =CELL#/100 and it is perfect nicely..but I have to make sure the decimal are in the right position. – PROBERT May 23 at 15:40
4

Use a formula to divide by 100.

enter image description here

  • 2
    +1 but I would use =IF(ABS(A2)>360,A2/100,A2) just to be on the safe side. ABS(A2) accounts for negative values and > 360 (or > 90 for N/S) detects that a value isn't already in range.. then just to be extra safe import the table into MS Access ensuring field names have no spaces or punctuation, before trying to utilize the data; Access tables are more stable than Excel spreadsheets due to strong typing of cell values. – Michael Stimson May 23 at 0:36
  • I googled earlier today and didn't work so this one I haven't seen so I will give this try. – PROBERT May 23 at 1:52
0

What I have also noticed that Latitude values in your table can have different length before a dot for example 3854.6 and 391.119. Moreover, IMHO your original values look more like text rather than real numbers.

Therefore, with the usage of the following formula, you can adjust your Latitude and Longitude values.

For Latitude values

=IF(FIND(",";VALUE(SUBSTITUTE(B2;".";",")))=5;VALUE(SUBSTITUTE(B2;".";","))/100;VALUE(SUBSTITUTE(B2;".";","))/10)

For Longitude values

=IF(FIND(",";VALUE(SUBSTITUTE(D2;".";",")))=7;VALUE(SUBSTITUTE(D2;".";","))/100;VALUE(SUBSTITUTE(D2;".";","))/10)

The output will look as following

Output1


As was previously specified in a comment by @Hans-Erren, if the value 391.119 should be 3901.119 instead, means that 0 was missing then use a different formula

=IF(FIND(",";VALUE(SUBSTITUTE(B2;".";",")))=5;VALUE(SUBSTITUTE(B2;".";","))/100;VALUE(LEFT(SUBSTITUTE(B3;".";",");FIND(",";SUBSTITUTE(B3;".";","))-2)&","&"0"&SUBSTITUTE(RIGHT(SUBSTITUTE(B3;".";",");LEN(SUBSTITUTE(B3;".";","))-FIND(",";SUBSTITUTE(B3;".";","))+2);",";"")))

Output2

  • Thank you but this is kinda of hard and complicated for a person who doesn't work in Excel much .... – PROBERT May 23 at 15:40
  • what do you think will be a better "workaround"? processing with pandas for instance? It is just that much complicated as much as the input data represented. And it directly responds to your comment "but I have to make sure the decimal are in the right position". – Taras May 23 at 15:42
  • It has nothing to do with yours it just if someone needs it really quick and the one I did was way easier and faster... but if you have lots of them like this then probably your might be better way and doesn't need right it away – PROBERT May 23 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.