‘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:
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.
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.
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 Else 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:
- Task Prioritization Matrix Template.
- Email Generator Template.
- Employee Leave Tracker Template.
- Employee Timesheet Calculator Template.
- Calendar Integrated with a To-Do-List Template.