Quickly Generate Military Alphabet Code for a Word in Excel

‘A’ as in Alpha, ‘B’ as in Bravo, ‘C’ as in Charlie. Heard these phrases before?

These are called the Military Alphabet Code (also known as NATO alphabet code). It is often used by people (especially the call center guys) to communicate the name, email address or the home/office address.

Generate Military Alphabet Code in Excel

In my first job, I got a project where I had to do cold calling to get some information on medical devices. Since we were connecting with people from all over the world with all kinds of accents, it was – at times – difficult to share our name and email or get theirs. It was then that I started relying on military alphabet codes to communicate clearly. I have seen a lot of call center guys do this effectively.

With this in mind, I have created an Excel Template where you can enter a text string, and it will automatically generate the Military Alphabet code for the entered text.

Something as shown below:

Generate Military Alphabet Code in Excel - Demo

As soon as you enter the text and hit Enter, it will automatically generate the military alphabet code for each alphabet in the text string.

Note that in this case, numbers and special characters would be shown as is. Also, this would work with either case – lower or upper.

Download the Military Alphabet Code Generator Template
Download File

Since this workbook contains a macro, as soon as you open it, you might see a yellow bar with the button – Enable Content. You need to click on this button for this to work.Generate Military Alphabet Code in Excel - Enable Content

How to Create this Military Alphabet Code Generator Template

This template works purely on VBA magic. There a couple of loops within the Worksheet Change event procedure that simply checks for each alphabet, and fetch the code word for that alphabet.

Here is the VBA code that does the work:

Private Sub Worksheet_Change(ByVal Target As Range)
'created by Sumit Bansal of TrumpExcel.com
Dim alphabetcount As Integer
Dim alphabet As String
Dim result As String
Dim i As Integer
Dim TargetColumn As Integer
Dim TargetRow As Integer
On Error Resume Next
TargetColumn = Target.Column
TargetRow = Target.Row

If TargetColumn = 4 And Cells(TargetRow, TargetColumn) = "" Then
Cells(TargetRow, TargetColumn + 1) = ""
Exit Sub
End If

If TargetColumn = 4 Then
alphabetcount = Len(Cells(TargetRow, TargetColumn))
For i = 1 To alphabetcount + 1
alphabet = Mid(Range(Target.Address), i, 1)
If Range("A2:A27").Find(alphabet) Is Nothing Then
result = result & ", " & alphabet
result = result & ", " & Range("A2:A27").Find(alphabet).Offset(0, 1)
End If
Next i
Cells(TargetRow, TargetColumn + 1) = Mid(result, 3, Len(result) - 4)
End If
End Sub

If you’d like to change a couple of these code words to suit your audience/region, you can simply change it in column B in the download file. The code would automatically pick the changed codes.

Note that since this workbook contains a macro, you need to save the file in .xls or .xlsm format.

I hope this would help you the next time you try and communicate names, email ids, or address with people over a telephonic conversation.

Do let me know what you think of this template by leaving a comment below.

Other Excel Templates You Might Like: