Convert Letters to Numbers in VBA & Excel Macros

VBA (Visual Basic for Applications) is Excel's built-in programming language, and it provides the Asc() and Chr() functions for converting between letters and numbers. Whether you need a custom worksheet function, a macro to batch-convert data, or column letter-to-number conversion, VBA handles it all. For quick conversions without opening Excel, use our letters to numbers converter.

The Asc() Function: Letter to Number

Asc() returns the ASCII code of the first character in a string. It works exactly like ord() in Python or PHP.

Sub BasicConversions()
    ' ASCII values
    Debug.Print Asc("A")   ' 65
    Debug.Print Asc("Z")   ' 90
    Debug.Print Asc("a")   ' 97

    ' Alphabet position (A=1, Z=26)
    Debug.Print Asc("A") - 64   ' 1
    Debug.Print Asc("M") - 64   ' 13
    Debug.Print Asc("Z") - 64   ' 26

    ' Handle lowercase
    Debug.Print Asc(UCase("m")) - 64   ' 13

    ' Reverse: number to letter
    Debug.Print Chr(65)          ' A
    Debug.Print Chr(13 + 64)     ' M
    Debug.Print Chr(26 + 64)     ' Z
End Sub

Custom Worksheet Function (UDF): Letter Position

Create a User Defined Function that you can call directly from any Excel cell, just like a built-in formula:

' Paste this into a VBA module (Alt+F11 → Insert → Module)

Public Function LetterPosition(letter As String) As Variant
    ' Returns the alphabet position of a single letter (A=1, Z=26)
    Dim c As String
    c = UCase(Left(Trim(letter), 1))

    If c >= "A" And c <= "Z" Then
        LetterPosition = Asc(c) - 64
    Else
        LetterPosition = CVErr(xlErrValue)  ' #VALUE! error
    End If
End Function

' Usage in Excel:
' =LetterPosition("A")    → 1
' =LetterPosition("m")    → 13
' =LetterPosition("Z")    → 26
' =LetterPosition("5")    → #VALUE!

A1Z26 Encode and Decode UDFs

Public Function A1Z26Encode( _
    text As String, _
    Optional sep As String = "-" _
) As String
    ' Converts text to A1Z26 number sequence
    ' =A1Z26Encode("Hello")      → "8-5-12-12-15"
    ' =A1Z26Encode("Hello",",")  → "8,5,12,12,15"

    Dim result As String
    Dim i As Long
    Dim c As String

    For i = 1 To Len(text)
        c = UCase(Mid(text, i, 1))
        If c >= "A" And c <= "Z" Then
            If result <> "" Then result = result & sep
            result = result & CStr(Asc(c) - 64)
        End If
    Next i

    A1Z26Encode = result
End Function

Public Function A1Z26Decode( _
    encoded As String, _
    Optional sep As String = "-" _
) As String
    ' Decodes A1Z26 numbers back to text
    ' =A1Z26Decode("8-5-12-12-15")  → "HELLO"

    Dim parts() As String
    Dim result As String
    Dim i As Long
    Dim num As Long

    parts = Split(encoded, sep)

    For i = LBound(parts) To UBound(parts)
        num = CLng(Trim(parts(i)))
        If num >= 1 And num <= 26 Then
            result = result & Chr(num + 64)
        End If
    Next i

    A1Z26Decode = result
End Function

These UDFs replicate what our A1Z26 cipher tool does in the browser, but directly inside your spreadsheet.

Word Value Calculator UDF

Public Function WordValue(text As String) As Long
    ' Calculates the sum of letter positions
    ' =WordValue("HELLO")  → 52 (8+5+12+12+15)
    ' =WordValue("EXCEL")  → 51 (5+24+3+5+12+2 → wait: E=5+X=24+C=3+E=5+L=12 = 49)

    Dim total As Long
    Dim i As Long
    Dim c As String

    For i = 1 To Len(text)
        c = UCase(Mid(text, i, 1))
        If c >= "A" And c <= "Z" Then
            total = total + (Asc(c) - 64)
        End If
    Next i

    WordValue = total
End Function

Column Letter Conversion

One of the most common VBA tasks is converting between Excel column letters (A, B, ..., AA, AB) and column numbers. This uses the same letter-to-number logic but with base-26 math. See our Excel column converter for an interactive version.

Public Function ColumnLetterToNumber(colLetter As String) As Long
    ' Converts column letter(s) to number
    ' =ColumnLetterToNumber("A")   → 1
    ' =ColumnLetterToNumber("Z")   → 26
    ' =ColumnLetterToNumber("AA")  → 27
    ' =ColumnLetterToNumber("AZ")  → 52
    ' =ColumnLetterToNumber("XFD") → 16384 (max Excel column)

    Dim result As Long
    Dim i As Long
    Dim c As String

    colLetter = UCase(Trim(colLetter))

    For i = 1 To Len(colLetter)
        c = Mid(colLetter, i, 1)
        result = result * 26 + (Asc(c) - 64)
    Next i

    ColumnLetterToNumber = result
End Function

Public Function ColumnNumberToLetter(colNum As Long) As String
    ' Converts column number to letter(s)
    ' =ColumnNumberToLetter(1)     → "A"
    ' =ColumnNumberToLetter(26)    → "Z"
    ' =ColumnNumberToLetter(27)    → "AA"
    ' =ColumnNumberToLetter(16384) → "XFD"

    Dim result As String
    Dim remainder As Long

    Do While colNum > 0
        remainder = ((colNum - 1) Mod 26)
        result = Chr(65 + remainder) & result
        colNum = (colNum - remainder - 1)  26
    Loop

    ColumnNumberToLetter = result
End Function

Batch Conversion Macro

This macro reads text from one column and writes the A1Z26 encoding to an adjacent column:

Sub BatchConvertColumn()
    ' Select the source column first, then run this macro
    ' It writes encoded values to the next column

    Dim rng As Range
    Dim cell As Range
    Dim outputCol As Long

    Set rng = Selection
    outputCol = rng.Column + 1

    For Each cell In rng
        If Len(cell.Value) > 0 Then
            Cells(cell.Row, outputCol).Value = _
                A1Z26Encode(CStr(cell.Value))
        End If
    Next cell

    MsgBox "Converted " & rng.Cells.Count & " cells!", _
        vbInformation, "Done"
End Sub

Unicode Support: AscW() and ChrW()

Sub UnicodeExample()
    ' AscW() returns full Unicode code point
    Debug.Print AscW("A")     ' 65 (same as Asc for ASCII)
    Debug.Print AscW("Z")     ' 90

    ' ChrW() creates Unicode characters
    Debug.Print ChrW(65)      ' A
    Debug.Print ChrW(8364)    ' € (Euro sign)

    ' For A-Z conversion, Asc() and AscW() are identical
    ' Use AscW() only if you need to handle non-ASCII characters
End Sub

Complete Working Example: Cipher Worksheet

Sub CreateCipherSheet()
    ' Creates a reference sheet with all letter-number mappings

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "A1Z26 Reference"

    ' Headers
    ws.Range("A1").Value = "Letter"
    ws.Range("B1").Value = "Position"
    ws.Range("C1").Value = "ASCII (Upper)"
    ws.Range("D1").Value = "ASCII (Lower)"
    ws.Range("E1").Value = "Hex"
    ws.Range("F1").Value = "Binary"

    ' Fill data
    Dim i As Long
    For i = 1 To 26
        ws.Cells(i + 1, 1).Value = Chr(64 + i)
        ws.Cells(i + 1, 2).Value = i
        ws.Cells(i + 1, 3).Value = 64 + i
        ws.Cells(i + 1, 4).Value = 96 + i
        ws.Cells(i + 1, 5).Value = "0x" & Hex(64 + i)
        ws.Cells(i + 1, 6).Value = _
            WorksheetFunction.Dec2Bin(64 + i, 8)
    Next i

    ' Format
    ws.Range("A1:F1").Font.Bold = True
    ws.Columns("A:F").AutoFit

    MsgBox "Reference sheet created!", vbInformation
End Sub