Formulas for converting coordinates & negative longitudes to Decimal degrees

**helsbelz** · 08-29-2014, 06:20 AM

I am curently converting some coordinates in the format 5817.47 into decimal degrees using the formula...

=LEFT(A2,2)+MID(A2,3,6)/60

I would like to know two things;

1. The original coordinates are in the form 58.1747 I have been manually retyping them to 58.1747 so that I can use the above formula, is there a formula that will convert straight from the 58.1747 format?

2. The above formula does not work for negative longitude coordinates in the format -6.07939 could anyone suggest a suitable method for negative numbers?

Helen

**Glenn Kennedy** · 08-29-2014, 06:40 AM

Try this for Northings (assuming your data start in A2)

Formula:

=CONCATENATE(TEXT(ROUNDDOWN(ABS(A2),0),"00"),"° ",TEXT(ROUNDDOWN(ABS((A2-ROUNDDOWN(A2,0))*60),0),"00"),"' ",TEXT(TRUNC((ABS((A2-ROUNDDOWN(A2,0))*60)-ROUNDDOWN(ABS((A2-ROUNDDOWN(A2,0))*60),0))*60,2),"00.00"),"""",IF(A2<0," S"," N"))

and this for Eastings (assuming data start in B2)

Formula:

=CONCATENATE(TEXT(ROUNDDOWN(ABS(B2),0),"000"),"° ",TEXT(ROUNDDOWN(ABS((B2-ROUNDDOWN(B2,0))*60),0),"00"),"' ",TEXT(TRUNC((ABS((B2-ROUNDDOWN(B2,0))*60)-ROUNDDOWN(ABS((B2-ROUNDDOWN(B2,0))*60),0))*60,2),"00.00"),"""",IF(B2<0," W"," E"))