How to Convert Lat Long to UTM in Excel (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel by Microsoft is, without question, one of the most powerful computer programs available. We can perform an almost infinite computation on any dataset using the features and tools that Excel provides. In this article, we will explore two straightforward methods anyone can use in Excel to convert latitude and longitude to UTM. Hence, you must follow these 2 simple ways to Convert Lat Long to UTM in Excel.


Download Practice Workbook

You are welcome to get a free copy of the example workbook just below here, and we used that all during the session.


2 Simple Methods to Convert Lat Long to UTM in Excel

The Universal Transverse Mercator (UTM) is a way to project maps and give coordinates to places on Earth’s surface. Like the old way of using latitude and longitude, this is a horizontal way to show where you are. The UTM system divides the Earth into 60 zones, each covering 6° longitude. A grid zone comprises both a location and a band of latitude. Sometimes, only N or S is added after the zone number to show the North or South hemisphere.

For illustration, the following data set has both latitude and longitude values. Using Google Earth, we get these values from some states in America and Africa. We’ll convert latitude and longitude to UTM in Excel using these two methods. Also, I have yet to say I’ve been writing this article with the Microsoft Excel 365 version. You are free to use whatever version works best for you.

convert lat long to utm excel


1. Combine IF, CONCATENATE, and INT Functions to Convert Lat Long to UTM in Excel

Here are the IF, CONCATENATE, and INT functions that we’ll apply in this method. The IF function lets you compare a value with what you expect logically. The CONCATENATE function enables us to merge the contents of many cells into one. Excel’s INT function retrieves the integer component of a decimal value by rounding it down. We can use these three functions to Convert Lat Long to UTM in Excel. Therefore, follow the steps below to perform the task.

STEPS:

  • Firstly, I start by entering the following Excel formula into the cell D5:
=IF(B5>=0,CONCATENATE(INT(31+C5/6),” N”),CONCATENATE(INT(31+C5/6),” S”))

Combine IF, CONCATENATE, and INT Functions to Convert Lat Long to UTM in Excel

  • After that, press the ENTER key to see the result, which is 13N.
  • Now, use the formula used in the D5 cell to get the UTM of the other Lat and Long values.
  • To achieve this, click on the Fill Handle icon.
  • Subsequently, hold it down, and drag it to cell D10.
  • As a result, it will return the desired output, as shown below.

🔎 How Does the Formula Work?

For this formula to make sense, you need to know how to use the following Excel functions:

INT, IF, and CONCATENATE

Now, observe these formulas.

  • INT(31+C5/6)

The mathematical expression gives the values – {13.36236052;11.56792704;11.18443451;14.66315629;11.98617117;34.99888883}

Then, by involving the INT function we get – {13;11;11;14;11;34}

  • CONCATENATE(INT(31+C5/6),” N”) or CONCATENATE(INT(31+C5/6),” S”)

CONCATENATE(INT(31+C5/6),” N”) joins the output value of INT(31+C5/6) with N. And CONCATENATE(INT(31+C5/6),” S”) merges the output value of INT(31+C5/6) with S. Here, CONCATENATE function concatenates N for true and S for false.

  • IF(B5>=0,CONCATENATE(INT(31+C5/6),” N”),CONCATENATE(INT(31+C5/6),” S”))

Lastly, the IF function evaluates cells B5 to B10 of our specified dataset – {True;True;True;True;True;False}

Therefore, we get – {13 N;11 N;11 N;14 N;11 N;34 S}

Read More: Convert Latitude and Longitude to Decimal Degrees in Excel


2. Transform Lat Long to UTM Through Excel VBA

Microsoft has created the Visual Basic for Application (VBA) programming language. VBA is used to execute various operations, and different user types operate the programming language for multiple purposes. Here we will Convert Lat Long to UTM using some functions of the VBA language. Hence, study the procedure detailed below.

STEPS:

  • At first, choose the active sheet of the workbook to begin.
  • Secondly, go to Developer.
  • Then, click on Visual Basic.

Transform Lat Long to UTM Through Excel VBA

  • Next, select Insert and later click Module.

  • After that, put the following code in the Module box.
Sub SOFTEKOlatLongToUtm()
Dim i As Integer
Dim lat As Double
Dim lng As Double
Dim UTM
Dim UTMint
For i = 5 To 10
lat = Cells(i, 2).Value
lng = Cells(i, 3).Value
UTM = 31 + lng / 6
UTMint = CInt(UTM)
If lat >= 0 Then
UTMint = UTMint & " N"
Else
UTMint = UTMint & " S"
End If
Cells(i, 4).Value = UTMint
Next
End Sub

  • Lastly, press F5 or click the Run button.
  • Thus, this is where the output will be directed.

Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)


Conclusion

From now on, you can convert Lat Long to UTM in Excel using the steps we just discussed. On the ExcelDemy website, you can find many articles like this one. Keep using those, and let us know if you have any other ways to get the work done or if you have any new ideas. Remember to post questions, comments, or suggestions in the section below.


Related Articles

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

2 Comments
  1. Thank you but this doesn’t convert lat long to utm. Just telling which zone it is.

    • Hello, AMIR!
      Thanks for your comment.

      Yes! The code you provided is a simple implementation for converting latitude and longitude to UTM in Excel VBA. However, it is not the most accurate way to convert coordinates, as it uses a simplified formula for converting latitude and longitude to UTM coordinates.

      The formula used in this code only accounts for the UTM zone and hemisphere based on the latitude and longitude values. It does not take into account the curvature of the Earth’s surface, which can lead to inaccuracies in the UTM coordinates. If you need this, then you can use the following code:

      Function LatLonToUTM(Lat As Double, Lon As Double) As String
          'Declare variables for UTM coordinates
          Dim Zone As Integer, Easting As Double, Northing As Double
          
          'Convert Lat/Lon to UTM using Proj4 library
          Dim Proj As Object
          Set Proj = CreateObject("OSGeo.OSR.SpatialReference")
          Proj.ImportFromEPSG 4326 ' WGS84
          Proj.SetUTM Zone(Lon), (Lat >= 0)
          
          Dim LatLon(0 To 1) As Double
          LatLon(0) = Lon
          LatLon(1) = Lat
          
          Dim xy(0 To 1) As Double
          Proj.TransformPoint xy(0), xy(1), 0, LatLon(0), LatLon(1), 0
          
          'Convert UTM coordinates to string format
          LatLonToUTM = Chr(Zone + 64) & " " & Format(Easting, "0.00") & " " & Format(Northing, "0.00")
      End Function
      Function Zone(Lon As Double) As Integer
          Zone = Int((Lon + 180) / 6) + 1
      End Function

      To use this code, open a new Excel workbook, press ALT+F11 to open the VBA editor, and insert a new module. Copy and paste the code into the module, and save the module.
      Then, in your Excel sheet, you can use the formula =LatLonToUTM(lat, lon) where lat and lon are the latitude and longitude coordinates you want to convert to UTM format.
      This code uses the Proj4 library to perform the coordinate transformation. You may need to install this library on your computer if it is not already installed.
      And if you don’t want to use this library. you can use the following code instead.

      Function LatLongToUTM(ByVal latitude As Double, ByVal longitude As Double) As String
          
          'Define constants for the conversion
          Const PI As Double = 3.14159265358979
          Const a As Double = 6378137
          Const b As Double = 6356752.3142
          Const k0 As Double = 0.9996
          
          'Convert latitude and longitude to radians
          Dim lat_rad As Double
          Dim long_rad As Double
          lat_rad = latitude * PI / 180
          long_rad = longitude * PI / 180
          
          'Define constants for the conversion
          Dim e As Double
          e = Sqr(1 - (b / a) ^ 2)
          Dim e1sq As Double
          e1sq = (e ^ 2) / (1 - e ^ 2)
          Dim n As Double
          n = (a - b) / (a + b)
          Dim rho As Double
          rho = a * (1 - e ^ 2) / (1 - (e ^ 2) * (Sin(lat_rad) ^ 2)) ^ 1.5
          Dim nu As Double
          nu = a / Sqr(1 - (e ^ 2) * (Sin(lat_rad) ^ 2))
          Dim psi As Double
          psi = nu / rho
          Dim v As Double
          v = (k0 * nu) / psi
          Dim p As Double
          p = long_rad - (2 * PI * Int((long_rad + PI) / (2 * PI)))
          Dim l As Double
          l = (p - 0.2617993877991495) * 180 / PI
          
          'Calculate UTM zone number
          Dim zone As Integer
          zone = Int((long_rad + PI) / (2 * PI)) + 1
          If zone > 60 Then
              zone = zone - 60
          End If
          
          'Calculate UTM x and y coordinates
          Dim x As Double
          Dim y As Double
          x = v * Sinh(l / (2 * 1.005037))
          y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))
          
          'Convert x and y to UTM coordinates
          x = x + 500000
          If y  60 Then
              zone = zone - 60
          End If
          
          'Calculate UTM x and y coordinates
          Dim x As Double
          Dim y As Double
          x = v * Sinh(l / (2 * 1.005037))
          y = v * (Cosh(l / (2 * 1.005037)) * Tanh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))) - (e * Atanh(e * Sinh(Asinh(Sin(lat_rad) / Sqrt(1 + e1sq * (Cos(lat_rad) ^ 2))))))))
          
          'Convert x and y to UTM coordinates
          x = x + 500000
          If y < 0 Then
              y = y + 10000000
          End If
          
          ' Define UTM zone letter
          Dim letters As String
          letters = "CDEFGHJKLMNPQRSTUVWXX"
          Dim zone_letter As String
          zone_letter = Mid(letters, zone, 1)
          
          ' Return UTM coordinates as string
          LatLongToUTM = zone & zone_letter & " " & Format(x, "0.00") & " " & Format(y, "0.00")
          
      End Function

      This code converts latitude and longitude coordinates to UTM coordinates and returns the result as a string in the format "Zone Letter X Y". You can call this function by passing the latitude and longitude values as parameters, like this:

      Sub ConvertLatLongToUTM()
          'Define latitude and longitude values
          Dim latitude As Double
          Dim longitude As Double
          latitude = 37.7749
          longitude = -122.4194
          
          'Call LatLongToUTM function
          Dim utm_coordinates As String
          utm_coordinates = LatLongToUTM(latitude, longitude)
          
          'Display UTM coordinates in message box
          MsgBox utm_coordinates
      
      End Sub

      Make sure you have the LatLongToUTM function defined in your VBA code module before running the ConvertLatLongToUTM sub.

      Hope this will help you.
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo