+ Reply to Thread
Results 1 to 6 of 6

Can't get excel to display latitude and longitude properly

  1. #1
    Registered User
    Join Date
    05-11-2020
    Location
    Brazil
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Can't get excel to display latitude and longitude properly

    Hi everyone, I'm need help with my worksheets. I have sheets with hundreds to thousands locations of gps coordinates each, but I can't get my excel to display them properly.

    When I use the format .csv all the columns get cramped into a single column, and the latitude/longitude data is displayed correctly (eg -64.789548), but once I separate the columns properly either by text to columns command, or by getting the data externally and choosing to separate it by commas all the lat/long data displays incorrectly (eg. -64.789.548).

    Is there any way to get the converted sheets to display them correctly? Preferably without losing the converted sheets, since there are too many sheets I'd have to convert again.

    I've tried to upload samples of both formats of worksheets.

    Thank you so much in advance for any help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,258

    Re: Can't get excel to display latitude and longitude properly

    I opened your .csv file in a text editor, and it looks normal there -- assuming that "." is the expected decimal separator. It appears to me that, when you imported into Excel, you used "." as the thousands separator (and, I assume, "," as the decimal separator) which caused Excel to treat the text -58.030128 as the number -58 million 30 thousand 128. Double check your text/csv import process and make sure it is using "." for the decimal separator.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-11-2020
    Location
    Brazil
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Can't get excel to display latitude and longitude properly

    Yes, the decimal separator was indeed wrong in the conversion process, thank you. But now that I switched it back to ".", my numbers show up as -58,030128 (with an "," instead of ".") which is better but still not ideal, do you have any idea on why that might be happening?

    Also, do you know if there's a way to fix the already converted tables that display, for instance, -58.030.128 or do I really need to convert them all back again?

    Thank you once more.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,258

    Re: Can't get excel to display latitude and longitude properly

    Also, do you know if there's a way to fix the already converted tables that display, for instance, -58.030.128
    If they are consistently 6 digits past the decimal point, then it should be as simple as dividing the numbers by 1E6.

    But now that I switched it back to ".", my numbers show up as -58,030128 (with an "," instead of ".") which is better but still not ideal, do you have any idea on why that might be happening?
    That looks like the .csv import process is using "." as the decimal separator, but Excel is still using "," as the decimal separator. I'm not sure exactly how you want these decimal degrees values displayed, but it still seems like it is about getting the correct decimal separator settings in all the right places.

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    939

    Re: Can't get excel to display latitude and longitude properly

    @Malebus;

    While using Text to Columns method in your CSV file, you can choose the Lat-Lon columns' format as text so that, data will appear as just seen on the CSV file.

    .

  6. #6
    Registered User
    Join Date
    05-11-2020
    Location
    Brazil
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Can't get excel to display latitude and longitude properly

    MrShorty

    They aren't consistently 6 digits, I guess I'll just do them all over again. I also made sure Excel was using '.' for decimal aswell, so I don't know why it still goes wrong and puts a ',' in its place... Thank you for all your help.

    Haluk

    This actually solves the problem of getting the number to display as it should, even though I don't understand what was causing the problem in the first place. Thank you.

+ 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. Latitude/longitude computation?
    By Hambone70 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-27-2014, 12:30 PM
  2. Replies: 0
    Last Post: 03-03-2014, 04:42 AM
  3. Excel VBA UK Addresses from PostCode or Longitude/Latitude
    By Niper in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 04:08 PM
  4. Replies: 1
    Last Post: 02-10-2006, 10:55 AM
  5. How do I enter latitude and longitude in Excel
    By Bernard McCartan in forum Excel General
    Replies: 2
    Last Post: 12-28-2005, 10:15 AM
  6. [SOLVED] Latitude/Longitude conversions in Excel - Please Help
    By knickel@columbus.rr.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-05-2005, 07:05 PM
  7. Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 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