Converting GPS decimal value coordinates to DMS coordinate/format using Numbers

Hi Whatzerface,

To my knowledge, Numbers does not have such a format (either built-in or custom).

The closest I can get is this:

Formula in E2 =B2&"°"&C2&"'"&D2&""""

Your original question was how to convert a decimal value to degrees, minutes and seconds. Your latest reply shows a Date and Time value in cell B3. I fail to see the connection to latitude and longitude 🤔.

Regards,

Ian.

Fairly easy process to describe: The letter, degrees and minutes will be the same in either notation, the decimal minutes are in 'milliminutes', and need to be separated from the left part of each coordinate, divided by 1000, then multiplied by 60.

Since dividing by 1000 then multiplying by 60 gives the same result as multiplying by 60/1000, and 60/1000 is the same as 0.060, which is equal to 0.06, we can skip the division and just multiply the decimal minute part by 0.06.

Expressed as a formula, that can be done as shown below:

The longitude and latitude of the GPS coordinates are entered into separate cells (B2 and C2).

The formula shown is entered in D2, then filled right to E2. The text copy below can be pasted into the formula editor for D2.

LEFT(B2,LEN(B2)−5)&"' "&ROUND(VALUE(MID(B2,FIND(".",B2)+1,3))×0.06,0)&""""

LEFT(B2,LEN(B2)−5)

This part gets left left part of the coordinate up to, but not including the decimal, last three digits and the minute sign at the end of the coordinate.

&"' "&

The ampersand is the concatenation operator. It connects the string on the left to the text (a minute sing and a space character) between the double quotes. The second ampersand connects the result of the rest of the formula to the string being built.

ROUND(VALUE(MID(B2,FIND(".",B2)+1,3))×0.06,0)

FIND finds the decimal point in the coordinate in B2, + adds 1, and the result is handed to MID

MID goes to the character determined by that number (the first digit after the decimal) and gets the three characters, starting at that position, and passes them (in order) to VALUE

VALUE converts the three character string to a number,

and ×0.06 multiplies that number by 0.06 to convert it to the equivalent number of seconds. and the result is handed to ROUND

ROUND rounds the result to the nearest whole second. and

&""""

The last concatenation operator attaches the seconds sign to the end of the string displayed in the cell.

Regards,

Barry

PS: If the GPS coordinates do not include a minute sign after the decimal minutes, change the -5 in the LEN function arguments to -4.

B.

Hi Whatzerface,

Please give some examples of the GPS decimal coordinates. I am thinking that they look like the example shown here:

https://www.rapidtables.com/convert/number/degrees-to-degrees-minutes-seconds.html

In Numbers,

Formula in B2 =INT(A2)

Formula in C2 =INT((A2−B2)×60)

Formula in D2 =INT((A2−B2−C2÷60)×3600)

More information will lead to a solution!

Regards,

Ian.

P.S. Please be aware that rescue services may require your location in degrees and decimal minutes.

Ian.