How to Convert Letters to Numbers in Excel

Converting letters to their alphabet position numbers is a common task in Excel, whether you are building a cipher encoder, analyzing text data, creating educational worksheets, or working with column numbering. Excel provides several built-in functions that make this conversion straightforward, and for more complex scenarios, VBA macros offer additional flexibility.

This tutorial covers every method for converting letters to numbers in Excel, from the simplest single-cell formula to batch processing entire columns and building custom VBA functions.

Method 1: The CODE() Function

The CODE() function returns the numeric ASCII code of the first character in a text string. Since uppercase A has ASCII code 65 and the alphabet position of A is 1, you can subtract 64 to get the position:

=CODE(UPPER(A1)) - 64

This formula works for any single letter in cell A1. The UPPER() wrapper ensures consistent results regardless of whether the input is uppercase or lowercase. Without it, lowercase "a" returns 97 instead of 65, giving you 33 instead of 1.

Understanding the Math

Method 2: Converting an Entire Word

To convert each letter of a word in a single cell to its position number, you need to extract individual characters using MID() and process them separately. Here is an approach using a helper column:

Cell A1: HELLO

Cell B1: =CODE(UPPER(MID($A$1, ROW(A1), 1))) - 64
  (drag down for as many rows as the word has letters)

B1 = 8  (H)
B2 = 5  (E)
B3 = 12 (L)
B4 = 12 (L)
B5 = 15 (O)

Concatenating Results

To join all the numbers into a single string with a separator, use the TEXTJOIN() function (Excel 2019 and later):

=TEXTJOIN("-", TRUE, B1:B5)
Result: 8-5-12-12-15

Method 3: VLOOKUP with a Reference Table

For those who prefer a visual approach, create a lookup table with letters in column D and numbers in column E (D1:E26), then use:

=VLOOKUP(UPPER(A1), $D$1:$E$26, 2, FALSE)

This method is easy to understand and modify. You can change the number assignments in the lookup table to create custom ciphers without altering any formulas.

Method 4: Using MATCH()

The MATCH() function finds the position of a value in a range. If you have A through Z in cells D1:D26:

=MATCH(UPPER(A1), $D$1:$D$26, 0)

This returns the position of the letter within the lookup range, which equals its alphabet position if your range starts with A.

Method 5: Array Formula for Full Words

For Excel 365 or Excel 2021 with dynamic arrays, this single formula converts an entire word to a hyphen-separated number string:

=TEXTJOIN("-", TRUE,
  CODE(UPPER(MID(A1, SEQUENCE(LEN(A1)), 1))) - 64
)

The SEQUENCE(LEN(A1)) generates numbers 1 through the length of the text. MID() extracts each character, CODE(UPPER())-64 converts it, and TEXTJOIN concatenates with hyphens.

Method 6: VBA Custom Function

For maximum flexibility, create a custom VBA function. Press Alt+F11 to open the VBA editor, insert a new module, and paste:

Function LetterToNum(text As String, _
    Optional sep As String = "-") As String
    Dim i As Long, c As String, result 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 & (Asc(c) - 64)
        End If
    Next i
    LetterToNum = result
End Function

Now you can use =LetterToNum(A1) in any cell. The function accepts an optional separator argument: =LetterToNum(A1, ",") uses commas instead of hyphens.

Converting Numbers Back to Letters

The reverse operation uses CHAR():

=CHAR(A1 + 64)

Where A1 contains a number from 1 to 26. This returns the uppercase letter at that alphabet position. For lowercase, use =CHAR(A1 + 96).

Handling Edge Cases

Non-Letter Characters

The CODE()-64 method returns unexpected results for digits, spaces, and symbols. Always validate input or add error handling:

=IF(AND(CODE(UPPER(A1))>=65, CODE(UPPER(A1))<=90),
    CODE(UPPER(A1))-64,
    "N/A"
)

Empty Cells

CODE("") returns a #VALUE! error. Wrap your formula in IFERROR() to handle this gracefully:

=IFERROR(CODE(UPPER(A1))-64, "")

Practical Applications

• Word value calculations: Sum all letter positions to get a word's numeric value. Use =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-64)
• Classroom worksheets: Create encoding/decoding exercises for students
• Data analysis: Convert categorical letter codes to numeric values for sorting and computation
• Column number lookup: Excel columns use the same A=1 system through Z=26, then AA=27