Convert degrees, minutes, seconds into decimal number and the opposite in Numbers

Question

Convert degrees, minutes, seconds into decimal number and the opposite in Numbers

Hello,

I am trying to convert a cell in Numbers (version 11.1), which contains a string like this 54° 38’ 30” to decimal format, like this 54.6416666667, without running an external script (I found a useful script by KOENIG Yvan here - thank you for that.). I am trying to add a function inside of the cell but I found difficulties in manipulating the string. Finally, it would be great having the opposite procedure, from a decimal number like 54.6416666667 return to a 54° 38’ 30” format.

It would be particularly convenient if this could also handle negative numbers like -54˚ 38' 40''.

Has anyone done something similar?

Posted on Sep 20, 2021 1:24 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Sep 20, 2021 1:02 PM in response to vasiliostakos

Going the other way, from Decimal to DMS, you could try something like this;

Formulas:

'

C2, filled down: =INT(B2)

D2, filled down: =INT((B2−C2)*60)

E2, filled down: =INT(((B2−C2)*60−D2)*60)

F2, filled down: =CONCATENATE(C2,"° ",D2, "’ ",E2,"""")

It is possible to get the expected values by combining this into one formula but I wasn't able to prevent Numbers from adding a lot of unwanted zeros. Maybe you will have better luck! (And sometimes it is better to leave work broken up into more manageable chunks in separate cell for debugging, understanding later what was done, etc.).

SG

Reply

Answer 2

Yellowbox

Level 7

21,915 points

Sep 20, 2021 4:16 AM in response to vasiliostakos

Hi vasiliostakos,

An answer to your first question.

I had to break the problem down into small steps, using extra columns.

Formula in B2: FIND(" ",A2)

Formula in C2: FIND(" ",A2,B2+1)

Formula in D2: LEFT(A2,B2−2)

Formula in E2: MID(A2,B2+1,C2−B2−2)

Formula in F2: MID(A2,C2+1,LEN(A2)−C2−1)

Formula in G2: E2÷60

Formula in H2: F2÷3600

Formula in I2: D2+G2+H2

Now hide columns B to F

Regards,

Ian.

Reply

Answer 3

Sep 20, 2021 6:47 AM in response to Yellowbox

Hello Ian and thank you very much. Just test it and it works perfectly on positive numbers! This is very helpful!

I am thinking if we could wrap everything to one cell and I will try an if function for negative numbers.

Reply

Answer 4

Sep 20, 2021 11:41 AM in response to vasiliostakos

Hello Ian,

I made some changes:

I substitute Formula in D2 with: IF(INT(LEFT(A2,B2−2))<0;LEFT(A2,B2−2)*-1;LEFT(A2,B2−2))

This will change any negative value into positive.

Then I changed also I2 with: IF(INT(LEFT(A2,B2−2))<0;LEFT(D2+G2+H2)*-1;LEFT(D2+G2+H2)

and the last one will change again into negative if the first given number of degrees is a negative!

Reply

Answer 5

Yellowbox

Level 7

21,915 points

Sep 20, 2021 5:02 AM in response to Yellowbox

Yellowbox wrote:

Now hide columns B to F

Correction: Now hide columns B to H

Reply

Answer 6

Sep 21, 2021 1:31 AM in response to SGIII

Thank you SG,

Because I wanted to check also for negative numbers I made some changes. I want degrees to stay as negative (if there are any) numbers, but not minutes and seconds. So I did this by creating one more cell.

C2: FIND(",";B2)

D2: LEFT(B2;C2-1)

E2: IF((B2-E2)<0;(B2-E2)x-1;B2-E2)

F2: IF(TRUNC(E2*60)<0;TRUNC(E2*60)*-1;TRUNC(E2*60))

G2: IF(INT(((E2*60)-F2)*60)<0;INT(((E2*60)-F2)*60)*-1;INT( ((E2*60)-F2)*60))

H2: CONCATENATE(D2&"˚"&E2&"'"&G2&"""")

I am sure that there should be an easier and smarter way. The only thing I should do is to start combine cells. Thank you SG.

Reply