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

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

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

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

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}