## Lats and Longs in Excel

### Lats and Longs in Excel

Help
Is there a way to make excel understand that the numbers I wish to type
into a single column is a latitude or longitude? At the moment i need to
use four seperate columns, degrees, minutes, seconds, and south or east. I
would appreciate any help with this.

### Lats and Longs in Excel

Quote:>Help
>Is there a way to make excel understand that the numbers I wish to type
>into a single column is a latitude or longitude? At the moment i need to
>use four seperate columns, degrees, minutes, seconds, and south or east. I
>would appreciate any help with this.

I was going to suggest that you use time, formatted in HH:MM:SS,
but the Excel doesn't like the hours to be larger than 24,
so unless you can keep your lats and longs to under 24, you can't
really get away with this.  It seems pretty absurd to me.  People
measure things other than time of day in hh:mm:ss.  Let me know
if you come up with something better.

--D
--
- - - - - -- - - - - - - - - -- - - - - - - - - - - - - - - - - - - - -

"If Santa Claus begins airlifting cakes, cookies, and clothing by
reindeer, should we shoot Rudolph out of the sky because we bake and
sew ourselves?"                       -- New Ideas From Dead Economists

### Lats and Longs in Excel

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Help
Is there a way to make excel understand that the numbers I wish to type
into a single column is a latitude or longitude? At the moment i need to
use four seperate columns, degrees, minutes, seconds, and south or east. I
would appreciate any help with this.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
I was going to suggest that you use time, formatted in HH:MM:SS,
but the Excel doesn't like the hours to be larger than 24,
so unless you can keep your lats and longs to under 24, you can't
really get away with this.  It seems pretty absurd to me.  People
measure things other than time of day in hh:mm:ss.  Let me know
if you come up with something better.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

The best solution I  can  think  of  is  creating  a custom number format
[Format|Number] in the form of "00:\00:\00" (minus the quotes,  of  course).
You  have  to enter the number as a 6-digit string (i.e.   606060),  but the
colons will be automatically entered.
I  can't  think  of  a  way to include south or east in the cell without
Excel thinking it's a  text  string,   though.    You  could have one format
"00:\00:\00 \S" and one as "00:\00:\00 \E",  but you'd have to write a macro
to choose between them easily.  Can anyone think of a way around this?
--

### Lats and Longs in Excel

>Help
>Is there a way to make excel understand that the numbers I wish to type
>into a single column is a latitude or longitude? At the moment i need to
>use four seperate columns, degrees, minutes, seconds, and south or east. I
>would appreciate any help with this.

It seems to me that there are 3 aspects of the problem you describe:
1) How to key in the data?
2) How to display the latitude or longitude in the speadsheet?
3) How to use the data in calculations?

1) Probably the easiest thing to do is adopt the convention that south
latitudes are negative and west longitudes are also negative.  Then it
makes most sense if you can just key in the degrees (signed 1-3 digits)
followed by a 2-digit value for minutes and a 2-digit value for seconds,
all in the same cell.

2) You'll need a custom format to display the data in the expected way.
As an example, here's one I've created for longitudes that seems to
work in Excel 4.0:
[>0]##0\* 00\' 00\" \E;##0\* 00\' 00\" \W
where the * represents the degree symbol (I'm using Excel for Mac, and
the degree symbol is obtained on the Mac by Option-Shift-8;
unfortunately I can't show that symbol here).  What this does is use one
format (displaying an E) for positive numbers (East longitudes) and
another (displaying a W) for negative numbers (West longitudes).

3) If you need to operate with this latitude or longitude (as a decimal
number), then you will need to create a formula to parse this string of
numbers, combine appropriately the 3 components, and then assign the
correct sign to the final value.  Not a trivial matter, but it can be
done fairly simply.  Let me know if you need some help doing this.

--
+----------------------------------------------------------------------+

+----------------------------------------------------------------------+

Hello To All:
at my work we get a lot of USA tickets(under ground alert service)
in which we have to locate for our facilities. the tickets do have lat
long information as to the location of the activity.  Our GIS map is in