Template to convert geographic coordinates to deg, UTM and AutoCAD

This Excel template is initially made to convert geographic coordinates in decimal to degrees, minutes and seconds. Just the opposite of the template that we had done before, as seen in this example:

In addition:

• Concatenate them into a string
• Turn them to UTM coordinates, with an option to choose a Datum
• Concatenates point command to create points in AutoCAD with a single copy / paste
• Concatenates the polyline command to draw the polygon with a copy / paste

It will be available for free for a few days so as faithful readers can download it.

How is it done?

• To condition the input fields, place the cells properties. This is done with the Data tab, in the Data Validation option. We chose to stand there only decimal data between -180 and 180 which is the maximum supported by longitudes. And then in the error message is indicated that data are not allowed. In the case of latitudes, are indicated between -90 and 90.
• To choose a hemisphere in longitudes, which are in G column, the cell is conditioned so that if the coordinate is negative text W is typed if it is positive the text placed is E.

This is done with the formula = IF (G37 <0, “W”, “E”)

• Similarly with the latitudes that are in column H, if the coordinate is negative, write the letter S, if positive the N.

The formula will be   =IF (H37<0,”N”,”S”)

• To remove the degrees, it is used the absolute value and the number is truncated to zero decimal = ABS (TRUNC (G37, 0)) in this way, -87.452140 will become 87
• To extract the minutes, subtract the original value from the truncated value, so that are left only the decimal (0.452140) and that value is multiplied by 60 which is the total minutes that are in a degree. It is truncated to zero decimal places and so it is obtained that there are 27 minutes in 0.452140 = TRUNC ((ABS (G37)-J37) * 60.0)
• 
• For the second, multiply the decimal (0.452140) by 3600 which is the number of seconds in a degree (60 × 60), and subtract what we have subtracted before, which are minutes (27) multiplied by 60. Then apply a fillet, with a reference cell where is the number of decimal places that can be adjusted to taste. So there are 7,704 seconds. = ROUND ((((ABS (G37)-J37)) * 3600) – (K37 * 60), $ L $ 5)
• To concatenate the command point, it is applied the chain _point so that it only be copied the cells to the AutoCAD command line = CONCATENATE (“_point”, ROUND (S37, 2), “,”, ROUND (T37, 2 )).
• Similarly is done with the polyline command = CONCATENATE (“_pline”, ROUND (S37, 2), “,”, ROUND (T37, 2)). It applies rounding so as not to become too long chains.

In the template there are some tips to run this last action.

From here you can download the template.

[paiddownloads id=”1″]

If you don´t have Paypal, you can buy it with Credit Card.