How to Convert Lat Long to UTM in Excel (with Easy Steps)

We’ll use the following dataset, which contains several coordinates with their latitude and longitude values, to convert to UTM values.

What Is UTM?

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

Convert Lat Long to UTM in Excel: Step-by-step Procedures

We will find the UTM Easting, UTM Northing, and UTM Zone from these Lat and Long values.

Step 1 – Implement Excel VBA Functions to Find UTM

Part 1.1 – User-defined Function for UTM Easting

Go to the Developer tab and click on Visual Basic.

Choose Insert and click on Module.
Paste in the following code and Save.

Function CalculateUTMEasting(latitude As Double, longitude As Double) As Double

    Dim utmZone As Integer
    utmZone = Int((longitude + 180) / 6) + 1

    Dim CentralMeridian As Double
    CentralMeridian = (utmZone - 1) * 6 - 180 + 3

    Dim ScaleFactor As Double
    ScaleFactor = 0.9996

    Dim EquatorialRadius As Double
    EquatorialRadius = 6378137#

    Dim EccentricitySquared As Double
    EccentricitySquared = 0.00669438

    Dim N As Double
    N = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(latitude * 3.14159265358979 / 180) ^ 2)

    Dim T As Double
    T = Tan(latitude * 3.14159265358979 / 180) ^ 2

    Dim C As Double
    C = EccentricitySquared * Cos(latitude * 3.14159265358979 / 180) ^ 2

    Dim A As Double
    A = Cos(latitude * 3.14159265358979 / 180) * (longitude - CentralMeridian) * 3.14159265358979 / 180

    Dim M As Double
    M = EquatorialRadius * ((1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256) * latitude * 3.14159265358979 / 180 - (3 * EccentricitySquared / 8 + 3 * EccentricitySquared ^ 2 / 32 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(2 * latitude * 3.14159265358979 / 180) + (15 * EccentricitySquared ^ 2 / 256 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(4 * latitude * 3.14159265358979 / 180) - (35 * EccentricitySquared ^ 3 / 3072) * Sin(6 * latitude * 3.14159265358979 / 180))

    Dim UTMEasting As Double
    UTMEasting = ScaleFactor * N * (A + (1 - T + C) * A ^ 3 / 6 + (5 - 18 * T + T ^ 2 + 72 * C - 58 * EccentricitySquared) * A ^ 5 / 120) + 500000

    CalculateUTMEasting = Format(UTMEasting, "0.00")

End Function

Part 1.2 – User-defined Function for UTM Northing

Open the VBA Editor.
Choose Insert and click on Module.
Paste in the following code and Save.

Function CalculateUTMNorthing(latitude As Double, longitude As Double) As Double
   
    Dim utmZone As Integer
    utmZone = Int((longitude + 180) / 6) + 1
   
    Dim hemisphere As Integer
    If latitude < 0 Then
        hemisphere = -1
    Else
        hemisphere = 1
    End If

    Dim CentralMeridian As Double
    CentralMeridian = (utmZone - 1) * 6 - 180 + 3

    Dim ScaleFactor As Double
    ScaleFactor = 0.9996

    Dim EquatorialRadius As Double
    EquatorialRadius = 6378137#

    Dim EccentricitySquared As Double
    EccentricitySquared = 0.00669438

    Dim N As Double
    N = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(latitude * 3.14159265358979 / 180) ^ 2)

    Dim T As Double
    T = Tan(latitude * 3.14159265358979 / 180) ^ 2

    Dim C As Double
    C = EccentricitySquared * Cos(latitude * 3.14159265358979 / 180) ^ 2

    Dim A As Double
    A = Cos(latitude * 3.14159265358979 / 180) * (longitude - CentralMeridian) * 3.14159265358979 / 180

    Dim M As Double
    M = EquatorialRadius * ((1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256) * latitude * 3.14159265358979 / 180 - (3 * EccentricitySquared / 8 + 3 * EccentricitySquared ^ 2 / 32 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(2 * latitude * 3.14159265358979 / 180) + (15 * EccentricitySquared ^ 2 / 256 + 45 * EccentricitySquared ^ 3 / 1024) * Sin(4 * latitude * 3.14159265358979 / 180) - (35 * EccentricitySquared ^ 3 / 3072) * Sin(6 * latitude * 3.14159265358979 / 180))

    Dim UTMScaleFactor As Double
    UTMScaleFactor = 0.9996
   
    Dim UTMFalseNorthing As Double
    If hemisphere = 1 Then
        UTMFalseNorthing = 0
    Else
        UTMFalseNorthing = 10000000
    End If
   
    Dim UTMNorthing As Double
    UTMNorthing = UTMScaleFactor * (M + N * Tan(latitude * 3.14159265358979 / 180) * (A ^ 2 / 2 + (5 - T + 9 * C + 4 * C ^ 2) * A ^ 4 / 24 + (61 - 58 * T + T ^ 2 + 600 * C - 330 * EccentricitySquared) * A ^ 6 / 720)) + UTMFalseNorthing

    CalculateUTMNorthing = Format(UTMNorthing, "0.00")

End Function

Part 1.3 – User-defined Function for UTM Zone

Open the VBA Editor.
Choose Insert and click on Module.
Paste in the following code and Save.

Function CalculateUTMZone(latitude As Double, longitude As Double) As String

    Dim utmZone As Integer
    Dim LatitudeBand As String
   
    utmZone = Int((longitude + 180) / 6) + 1
   
    LatitudeBand = GetLatitudeBand(latitude)

    CalculateUTMZone = utmZone & LatitudeBand

End Function

Function GetLatitudeBand(latitude As Double) As String

    Dim LatitudeBand As String
   
    If latitude >= 0 Then
        Select Case latitude
            Case 0 To 8: LatitudeBand = "N"
            Case 8 To 16: LatitudeBand = "P"
            Case 16 To 24: LatitudeBand = "Q"
            Case 24 To 32: LatitudeBand = "R"
            Case 32 To 40: LatitudeBand = "S"
            Case 40 To 48: LatitudeBand = "T"
            Case 48 To 56: LatitudeBand = "U"
            Case 56 To 64: LatitudeBand = "V"
            Case 64 To 72: LatitudeBand = "W"
            Case Else: LatitudeBand = "X"
        End Select
    Else
        Select Case -latitude
            Case 0 To 8: LatitudeBand = "M"
            Case 8 To 16: LatitudeBand = "L"
            Case 16 To 24: LatitudeBand = "K"
            Case 24 To 32: LatitudeBand = "J"
            Case 32 To 40: LatitudeBand = "H"
            Case 40 To 48: LatitudeBand = "G"
            Case 48 To 56: LatitudeBand = "F"
            Case 56 To 64: LatitudeBand = "E"
            Case 64 To 72: LatitudeBand = "D"
            Case Else: LatitudeBand = "C"
        End Select
    End If
   
    GetLatitudeBand = LatitudeBand
   
End Function

Step 2 – Convert Lat Long to UTM Easting

Choose cell D6.
Insert the following formula.

=CalculateUTMEasting(B6,C6)

Hit Enter.
Drag the Fill Handle icon to D13.

Step 3 – Convert Lat Long to UTM Northing

Choose cell E6.
Insert the following formula.

=CalculateUTMNorthing(B6,C6)

Hit Enter.
Drag the Fill Handle icon to E13.

Step 4 – Convert Lat Long to UTM Zone

Choose cell F6.
Insert the following formula.

=CalculateUTMZone(B6,C6)

Hit Enter.
Drag the Fill Handle icon to F13.

Convert Lat Long to UTM Zone in Excel: 2 Simple Methods

For illustration, the following data set has both latitude and longitude values. We’ll convert latitude and longitude to UTM in Excel.

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

Steps:

Enter the following Excel formula into cell D5.

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

Hit Enter.

Drag the Fill Handle to cell D10.

How Does the Formula Work?

INT(31+C5/6)

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

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”))

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}

Method 2 – Transform Lat Long to UTM Through Excel VBA

Steps:

Go to the Developer tab.
Click on Visual Basic.

Select Insert and click Module.

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

Press F5 or click the Run button.

Download the Practice Workbook

Convert Lat Long to UTM.xlsm

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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 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

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

Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range Dim funcName As String funcName = "CalculateUTM" For Each cell In Target If InStr(1, cell.Formula, funcName) > 0 Then cell.NumberFormat = "0.00" End If Next cell End Sub

Function CalculateLatitude(UTMNorthing As Double, UTMZone As Integer) As Double Dim EquatorialRadius As Double EquatorialRadius = 6378137# Dim EccentricitySquared As Double EccentricitySquared = 0.00669438 Dim UTMScaleFactor As Double UTMScaleFactor = 0.9996 Dim UTMFalseNorthing As Double If UTMNorthing < 0 Then UTMFalseNorthing = 10000000 Else UTMFalseNorthing = 0 End If Dim M As Double M = UTMNorthing / UTMScaleFactor - UTMFalseNorthing Dim Mu As Double Mu = M / (EquatorialRadius * (1 - EccentricitySquared / 4 - 3 * EccentricitySquared ^ 2 / 64 - 5 * EccentricitySquared ^ 3 / 256)) Dim Phi1Rad As Double Phi1Rad = Mu + (3 * EccentricitySquared / 2 - 27 * EccentricitySquared ^ 3 / 32) * Sin(2 * Mu) _ + (21 * EccentricitySquared ^ 2 / 16 - 55 * EccentricitySquared ^ 4 / 32) * Sin(4 * Mu) _ + (151 * EccentricitySquared ^ 3 / 96) * Sin(6 * Mu) _ + (1097 * EccentricitySquared ^ 4 / 512) * Sin(8 * Mu) Dim phi1 As Double phi1 = Phi1Rad * 180 / 3.14159265358979 Dim N1 As Double N1 = EquatorialRadius / Sqr(1 - EccentricitySquared * Sin(Phi1Rad) ^ 2) Dim T1 As Double T1 = Tan(Phi1Rad) ^ 2 Dim C1 As Double C1 = EccentricitySquared * Cos(Phi1Rad) ^ 2 Dim R1 As Double R1 = EquatorialRadius * (1 - EccentricitySquared) / ((1 - EccentricitySquared * Sin(Phi1Rad) ^ 2) ^ 1.5) Dim D As Double D = (UTMNorthing / (N1 * UTMScaleFactor)) Dim Latitude As Double Latitude = Phi1Rad - (N1 * Tan(Phi1Rad) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * EccentricitySquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 3 * C1 ^ 2 - 252 * EccentricitySquared) * D ^ 6 / 720) CalculateLatitude = Latitude * 180 / 3.14159265358979 End Function Function CalculateLongitude(UTMEasting As Double, UTMZone As Integer) As Double Dim CentralMeridian As Double CentralMeridian = (UTMZone - 1) * 6 - 180 + 3 Dim ScaleFactor As Double ScaleFactor = 0.9996 Dim EquatorialRadius As Double EquatorialRadius = 6378137# Dim EccentricitySquared As Double EccentricitySquared = 0.00669438 Dim UTMEastingShifted As Double UTMEastingShifted = UTMEasting - 500000 Dim A1 As Double A1 = Cos(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) Dim A2 As Double A2 = -Sin(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) Dim A3 As Double A3 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) Dim A4 As Double A4 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 3 * (1 + Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2) / 2 Dim A5 As Double A5 = Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 5 * (5 + 3 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 6 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 24 Dim A6 As Double A6 = -Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) * UTMEastingShifted / (EquatorialRadius * ScaleFactor) ^ 7 * (61 + 90 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 2 + 45 * Tan(CalculateLatitude(0, UTMZone) * 3.14159265358979 / 180) ^ 4) / 720 Dim Longitude As Double Longitude = CentralMeridian + (A1 + A2 + A3 + A4 + A5 + A6) * 180 / 3.14159265358979 CalculateLongitude = Longitude End Function Function GetUTMZoneNumber(UTMZone As String) As Integer GetUTMZoneNumber = Val(Left(UTMZone, Len(UTMZone) - 1)) End Function

23 Comments

Amir Feb 13, 2023 at 3:57 PM

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

Lutfor Rahman Shimanto Aug 13, 2023 at 12:37 PM

Dear Amir

Sorry for the late reply. Thanks a lot for bringing the issue to us. I have developed some user-defined functions using Excel VBA. These functions take Lat and Long values and return UTM Easting, Northing and Zone. I also have modified the article accordingly. So, I recommend you to go through the article again. You will not be disappointed, I promise.

Regards
Lutfor Rahman Shimanto

Sabrina Ayon Feb 15, 2023 at 2:54 PM

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 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.

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:

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.

Moses Aug 9, 2023 at 12:02 PM

Nothing on Eastings and Northing, its all about zoning only.
- Reply
  Lutfor Rahman Shimanto Aug 13, 2023 at 12:34 PM
  
  Hello MOSES
  
  Thanks for reaching out and posting your comment. You are absolutely right. However, I am delighted to inform you that I have modified the article. After going through the article, you must be able to convert Lat Long to UTM Easting, Northing and Zone. I have developed some user-defined functions using Excel VBA to achieve the goal.
  
  Regards
  Lutfor Rahman Shimanto
Faisal Aug 15, 2023 at 1:51 AM

Hi

This article has been extremely helpful, The codes above worked for Easting and Zone but not the Northing UTM…. I can’t seem to find a fix that works at the moment, even with your numbers it wouldn’t work. Any tips?
- Reply
  Lutfor Rahman Shimanto Aug 16, 2023 at 3:22 PM
  Dear Faisal
  
  Thanks a ton. Your appreciations mean a lot to us. I am glad you have found the article very helpful.
  
  You mentioned the user-defined functions only work for UTM Zone and UTM Easting on your end. The user-defined function for calculating UTM Northing is not working.
  
  However, the issue you are addressing is unclear to us. On our end, it is working perfectly. I am using Microsoft 365. However, It should not make any difference. The code should work for all versions of Excel.
  
  Please share your workbook containing a dataset with us. You can also post your query and attach the dataset in ExcelDemy Forum. We need more information about your problem, such as if it returns any error or wrong values.
  
  The functions often may return values using scientific notation like 2.34344E+13. To avoid this type of notation, you may use an Event procedure.
  
  Excel VBA Event Procedure:
```
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim funcName As String

    funcName = "CalculateUTM"

    For Each cell In Target
        If InStr(1, cell.Formula, funcName) > 0 Then
            cell.NumberFormat = "0.00"
        End If
    Next cell

End Sub
```
  I am looking forward to receiving your reply. Stay blessed and Good luck.
  
  Regards
  Lutfor Rahman Shimanto
xu Nov 18, 2023 at 11:00 PM

hi ,sir
you table northing cannot use ,it doesnot working ,would you please update it again ,we use the office 2016 or 2019,thanks .
- Reply
  Lutfor Rahman Shimanto Nov 20, 2023 at 3:11 PM
  
  Hello XU
  
  Thanks for reaching out. All the User-Defined functions mentioned in this article calculate UTM Easting, UTM Northing and UTM Zone accurately.
  
  I am comparing the result with an Online UTM Calculator website (LatLong.net) like the image below for demonstration.
  
  If you still have doubts, you can share your dataset in our ExcelDemy Forum. Good luck!
  
  Regards
  Lutfor Rahman Shimanto

xu Nov 19, 2023 at 1:58 PM

hi sir ,would u please write UTM convert to Lat long we want to this ,thanks so much.

Lutfor Rahman Shimanto Nov 20, 2023 at 3:16 PM

Hello XU

Thanks for thanking me. Your appreciation means a lot to us. You wanted to convert UTM (UTM Easting, UTM Northing and UTM Zone) to Latitude and Longitude values.

I am presenting some Excel VBA User-Defined functions, and these functions will achieve your goal.

Follow these Steps:

Step 1: Open the VBA Editor window => Paste the following code in a module => Save.

Step 2: Return to the sheet => Choose cell E4 => Insert the following formula => And drag the Fill Handle icon to cell E11.

=CalculateLatitude(C4,GetUTMZoneNumber(D4))

Step 3: Choose cell F4 => Insert the following formula => And drag the Fill Handle icon to cell F11.

=CalculateLongitude(B4,GetUTMZoneNumber(D4))

Things to Keep in Mind:
As we cannot use all decimal points for some values (UTM Northing), we may not get the same value (Latitude) like previous one.

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto

Christof Jan 16, 2024 at 4:46 PM

The CalculateUTMNorthing function is simply giving a #VALUE! error on Excel. Not really sure how to debug this because it is very generic
- Reply
  Lutfor Rahman Shimanto Feb 7, 2024 at 4:48 PM
  
  Hello CHRISTOF
  
  Thanks for sharing your queries. Depending on your OS Version, the User-defined functions may give this type of error. Ensure you run a 64-bit Windows, and let us know if you still face the error.
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy
Diana Mar 1, 2024 at 1:21 AM

Hello, thank you for the codes. Extremely helpful. However, when I entered the codes and then calculate the Northing, I got “#value” as an output. Wonder if you can help.
- Reply
  Lutfor Rahman Shimanto Mar 7, 2024 at 6:05 PM
  
  Hello! DIANA. Thanks for sharing your queries. Depending on your OS version, the user-defined functions may cause this error. Ensure you run 64-bit Windows, and let us know if you are still facing the error.
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy
Alan Jorge Innes Mar 5, 2024 at 3:17 AM

Hi I have a question. I am trying to use your code and it worked for UTM Easting and UTM zone but I am getting a “#VALUE!” error for northing. Please contact me and I can send you my dataset if you are able to help. Thank you.
- Reply
  Lutfor Rahman Shimanto Mar 7, 2024 at 6:10 PM
  
  Hello ALAN
  
  Thank you for reaching out. Please ensure that you’re using a 64-bit Windows system. Let us know if the issue persists.
  
  You can share your dataset by creating a Conversation with my forum account in the ExcelDemy Forum.
  
  Best regards
  Lutfor Rahman Shimanto
  ExcelDemy
Chansokong Nov 19, 2024 at 10:28 AM

I’ve already done every step, UTM to Easting and UTM Zone is done but it didn’t work on UTM to Nothing (Excel shown as “#VALUE!).
- Reply
  Shamima Sultana Nov 19, 2024 at 12:19 PM
  
  Hello Chansokong,
  
  Thank you for your comment! The #VALUE! error often occurs if there’s an issue with the formula or input data. Please double-check that all cell references are correct and that your latitude and longitude values are formatted properly. Ensure the cells don’t contain extra spaces or invalid characters.
  If the issue persists, feel free to share more details about your setup, and we’ll be happy to assist further!
  
  Regards
  ExcelDemy
  - Reply
    Chansokong Nov 26, 2024 at 7:57 AM
    
    Hello Shamima Sultana,
    I am trying to double check everything and it worked for UTM Easting and UTM zone but I am getting a “#VALUE!” error for northing.
    
    Best regards
    Chansokong
    - Shamima Sultana Nov 26, 2024 at 6:59 PM
      
      Hello Chansokong,
      
      Thank you for the update! The #VALUE! error for Northing might be due to incorrect references, data format, or an issue with the latitude values. Make sure the formula is correctly applied, the latitude and longitude are in decimal degrees, and there are no extra spaces or non-numeric characters. Additionally, ensure the latitude falls within the valid range for UTM zones.
      
      In our end everything is working perfectly.
      
      If everything seems correct, feel free to share your formula or more details, and I’d be happy to assist further!
      
      Regards
      ExcelDemy
narol jnr akis Nov 22, 2024 at 10:54 AM

please how do i donwnload this datasheet
- Reply
  Shamima Sultana Nov 23, 2024 at 11:11 AM
  
  Hello Narol Jnr Akis,
  
  To download the practice workbook or datasheet go to the Download the Practice Workbooksection. You can download the Excel file from there.
  
  Regards
  ExcelDemy