The quickest decimal to DMS convertion in Excel

by ·

Coordinates DMS to decimal and decimal to DMS one cell only Excel

Suppose you work on geographical or astronomical projects requiring transformation between the decimal and degree-minute-second pattern. You probably want to have everything converted as quickly as possible. On the web, some tutorials explain that the most common solution is using the Data -> Text to Columns with the main ribbon. Another way presented in the links is to play around with the VBA script, which can also do the work. This short article explains how it could be done quickly by using the formula. Similar approaches can already be found on the web. There are at least a few of them, but we need to know that the pattern of decimal degrees might vary across the devices and files. My formulae cover the three most common examples below:

Example 1

The standard Degree Minute Second notation is 28°28’26.4″.

If we want to convert it to decimal, we would need the following formula:

=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600

You can also find it at this link.

The TEXTBEFORE and TEXTAFTER functions trim our string before or after the character. In cases like ours, we need to use them in nested form because our DMS coordinates, written in string, eventually include three characters (degree, minute, and second symbol). The most problematic is the “second” symbol, expressed by quotes. They must be doubled if we want to bind them in our formula.

The first example was the simplest, excluding the situation when our decimal coordinates are negative. In such a situation, our formula must be expanded by the condition of the COUNTIF function.

=IF(COUNTIF(A3,"*S*"),(TEXTBEFORE(A3, "°")+TEXTBEFORE(TEXTAFTER(A3, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A3,"'"),"""")/3600)*-1,
TEXTBEFORE(A3, "°")+TEXTBEFORE(TEXTAFTER(A3, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A3,"'"),"""")/3600)

This is because we are setting the conditions for negative and positive values corresponding to the northern and southern parts of the Globe accordingly—the same we must do with the longitude later on.

=IF(COUNTIF(A4,"*W*"),(TEXTBEFORE(A4, "°")+TEXTBEFORE(TEXTAFTER(A4, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A4,"'"),"""")/3600)*-1,
TEXTBEFORE(A4, "°")+TEXTBEFORE(TEXTAFTER(A4, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A4,"'"),"""")/3600)

Finally, you should have results like these on the left. The notation of 28°28’26.4″ should result in the decimal value of 28.474 unless we specify the relation against the central meridian or equator. In that case, our decimal value will be positive or negative.
Let’s convert the opposite and return to the Degree Minute Second notation from Decimals. In this case, our formula will look as presented below.

=ROUNDDOWN((D2),0)&"°"&ROUNDDOWN((ABS(D2-TRUNC(D2))*60),0)&"'"&
ROUND(((ABS(ROUNDDOWN((ABS(D2-TRUNC(D2))*3600),3))/60-TRUNC((ROUNDDOWN((ABS(D2-TRUNC(D2))*3600),0))/60))*60),2)&""""

This case requires the concatenation, although it’s not the “classic” CONCATENATE function. This combines the formula body with the loose string we want to include in our final result. By looking at the entire notation, you can clearly distinguish these two elements connected by the & symbol. Next, the loose string is bound in quotes. This is a nice representation of output, which includes something extra in the formula, for example, the nomenclature of units and so forth, and allows the user to bind everything within one cell. In our case, the formula will bring our calculations back to the DMS system. Apart from that, the pivot work is completed by the TRUNC function, which separates the decimals from the entire number. For the second extraction, we need the TRUNC function to be nested, as only the piece of decimals is needed. The ABS function keeps everything in absolute value; hence, we avoid unforeseen negative results.
Let’s go further and consider how it works in the case of negative and positive coordinates, as discussed previously.

=IF(E2<0,ROUNDDOWN((D3),0)&"°"&ROUNDDOWN((ABS(D3-TRUNC(D3))*60),0)&"'"&
ROUND(((ABS(ROUNDDOWN((ABS(D3-TRUNC(D3))*3600),3))/60-TRUNC((ROUNDDOWN((ABS(D3-TRUNC(D3))*3600),0))/60))*60),2)&"""S",
ROUNDDOWN((D3),0)&"°"&ROUNDDOWN((ABS(D3-TRUNC(D3))*60),0)&"'"&
ROUND(((ABS(ROUNDDOWN((ABS(D3-TRUNC(D3))*3600),3))/60-TRUNC((ROUNDDOWN((ABS(D3-TRUNC(D3))*3600),0))/60))*60),2)&""" N")

The IF condition is helpful here. Instead of single quotes at the end, we specify which part of the globe coordinates refer to. Finally, our approach looks as follows (Pic. 1).

Excel degree minutes second to decimal degrees convertion

Example 2

The initial Degree Minute Second notation is N51° 52.394′. In this case, the formula will be slightly different, as you see below:

=SUBSTITUTE(TEXTBEFORE(A6,"°"),"N","")+(SUBSTITUTE(TEXTAFTER(A6,"°"),"'",""))/60

and it will be less complicated because we don’t have seconds this time. However, on the other hand, we have specified the hemisphere by the “N” symbol, which must be removed, the same as the minute ” ‘ ” symbol at the end. For this reason, the SUBSTITUTE function is required.  The same as in the previous example, the following formula must be considered concerning the given hemisphere:

=IF(D7<0,"W"&ROUNDDOWN(ABS(D7),0)&"°"&ROUND((ABS(D7-TRUNC(D7))*60),3)&"'","E"&ROUNDDOWN((D7),0)&"°"&
ROUND(((D7-TRUNC(D7))*60),3)&"'")

where again, the IF condition will be helpful. Finally, from the initial notation of N51° 52.394′, we should get -15.5821.
By returning to the original degree, minute, and decimal notation, our formula should look as follows:

="N"&ROUNDDOWN((D6),0)&"°"&ROUND(((D6-TRUNC(D6))*60),3)&"'"

and for specific hemispheres:

=IF(D7<0,"W"&ROUNDDOWN(ABS(D7),0)&"°"&ROUND((ABS(D7-TRUNC(D7))*60),3)&"'","E"&ROUNDDOWN((D7),0)&"°"&
ROUND(((D7-TRUNC(D7))*60),3)&"'")

Occasionally, you can see the ROUNDDOWN and ROUND functions. The ROUNDDOWN function is mandatory, as we need to define the whole number of degrees. ROUND is used to decrease the final number of decimals. In conclusion, the ROUNDDOWN must always be finished with 0, as we need only the whole numbers. The ROUND can finish with any number, preferably not smaller than 3. If you copy the place coordinates from Google Maps, you can notice that they include numbers with five decimals. Applying the formula above, our value should be transferred from -15.5821 to W15°34.925′ as displayed below (Pic. 2).

 

Excel degree minutes second to decimal degrees convertion2

Example 3

This example is the most advanced, except for the degree, minute, and second notation. We have also determined the hemisphere. The appropriate formula must reflect this case…

=SUBSTITUTE(TEXTBEFORE(A12,"°"),"N","")+MID(A12, SEARCH("°", A12)+1, SEARCH("'", A12) - SEARCH("°", A12) -1)/60+
(SUBSTITUTE(TEXTAFTER(A12,"'"),"""",""))/3600

mainly since the data must also be extracted from the middle; again, the SUBSTITUTE function is in place, this time along with the MID function, which can participate here as an alternative to nested TEXTAFTER with TEXTBEFORE. The same as before, the case is considered concerning different hemispheres, which are reflected in the formula below:

=IF(COUNTIF(A14,"*N*"),SUBSTITUTE(TEXTBEFORE(A14,"°"),"N","")+
MID(A14,SEARCH("°",A14)+1,SEARCH("'",A14)-SEARCH("°",A14)-1)/60+
(SUBSTITUTE(TEXTAFTER(A14,"'"),"""",""))/3600,(SUBSTITUTE(TEXTBEFORE(A14,"°"),"S","")+MID(A14,SEARCH("°",A14)+
1,SEARCH("'",A14)-SEARCH("°",A14)-1)/60+(SUBSTITUTE(TEXTAFTER(A14,"'"),"""",""))/3600)*-1)

since the hemisphere is defined, we need to use the COUNTIF function, which makes the action once the interesting part of the string (bound in wildcard) is appropriate. When our DMS notation is S 40°43’43”, then the outcome should be -40.7286111111111 unless we apply the ROUND function, then it can be -40.72861. For specifying the number of decimals, the formula should look slightly more advanced:

=IF(COUNTIF(A14,"*N*"),ROUND((SUBSTITUTE(TEXTBEFORE(A14,"°"),"N","")+MID(A14,SEARCH("°",A14)+1,SEARCH("'",A14)-
SEARCH("°",A14)-1)/60+(SUBSTITUTE(TEXTAFTER(A14,"'"),"""",""))/3600),5),ROUND(((SUBSTITUTE(TEXTBEFORE(A14,"°"),
"S","")+MID(A14,SEARCH("°",A14)+1,SEARCH("'",A14)-SEARCH("°",A14)-1)/60+
(SUBSTITUTE(TEXTAFTER(A14,"'"),"""",""))/3600)*-1),5))

In the opposite direction, the following formula will be applied:

=IF(D14>0,"N "&ROUNDDOWN((D14),0)&"°"&ROUNDDOWN(((D14-TRUNC(D14))*60),0)&"'"&ROUND(((ABS(ROUNDDOWN((ABS(D14-
TRUNC(D14))*3600),3))/60-TRUNC((ROUNDDOWN((ABS(D14-TRUNC(D14))*3600),0))/60))*60),2)&"""","S "&ROUNDDOWN((D14),0)&
"°"&ROUNDDOWN(((D14-TRUNC(D14))*60),0)&"'"&ROUND(((ABS(ROUNDDOWN((ABS(D14-TRUNC(D14))*3600),3))/60-
TRUNC((ROUNDDOWN((ABS(D14-TRUNC(D14))*3600),0))/60))*60),2)&"""")

You can see below for receiving an initial DMS notation result from S -40°-43’43” from -40.7286 (Pic. 3).

Excel degree minutes second to decimal degrees convertion3

To Wrap up this short text, I can say that this method is much more convenient than the traditional one, which requires the data delimitation mentioned at the beginning. Besides, you don’t need to “reserve” additional columns to extract your delimited data because everything shown here can be wrapped up in one cell only and dragged down to the end of your range of data.

 

Mariusz Krukar

 

Links:

  1. Calculatorsoup.com: Convert decimal degrees to degrees minutes seconds
  2. Rapidtables.com: Decimal degrees to degrees minutes seconds
  3. Rapidtables.com: Degrees minutes seconds to decimal degrees
  4. Learn.microsoft.com: Convert degrees minutes seconds angles with VBA
  5. Statology.org: Excel convert degrees minutes seconds to decimal degrees
  6. https://www.caliper.com/learning/how-can-i-convert-dms-to-decimal-degrees/?srsltid=AfmBOoq_Ht-U_9FHj7RahIXLU2qCpx-kOTd18P0YTzMsjSl0tVN_BEJk
  7. https://spreadsheetplanet.com/convert-latitude-longitude-to-decimal-degrees-excel/
  8. https://excelx.com/if-cell-contains/partial-text/
  9. https://www.ablebits.com/office-addins-blog/excel-textbefore-function-extract-text-before-character/
  10. https://www.ablebits.com/office-addins-blog/extract-text-between-two-characters-excel-google/
  11. Geocachingtoolbox.com: What3words to coordinates converter

 

Forums:

  1. https://rpls.com/forums/strictly-surveying/excel-formulas-for-dms-to-d-dddd-and-d-dddd-to-dms/
  2. https://www.reddit.com/r/excel/comments/1baj4sg/how_do_i_convert_from_degrees_minutes_seconds_to/
  3. https://stackoverflow.com/questions/19160113/convert-degree-minutes-to-decimal-degrees
  4. https://superuser.com/questions/705208/how-to-write-down-degrees-minutes-seconds-in-excel-and-convert-them-later-to-de
  5. https://www.quora.com/How-do-I-convert-a-decimal-to-degree-minutes-and-seconds-in-Excel

 

Youtube:

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *