How do you know whether you should use Excel Formulas or Excel VBA? Sometimes both work and sometimes only one works. This series will explore this question. Let’s start with an idea from Sumit Bansal at TrumpExcel.
This is what Sumit’s VBA code does
(Who is Sumit? Scroll to bottom of this post)
Type in a word. Sumit’s ‘Code‘ returns a word for each letter in your word. This string of words is used to avoid confusion when verbally sharing a word or a code. Imagine that you work at a military base. You don’t want to confuse “Lunch” with “Launch”! (further reading: Wikipedia article)
Can Formulas Also Solve This?
Yes. Formulas can also do this. This is an interesting case in which both methods work.
Which Solution Is Best?
Both solutions require some effort to understand. If you love VBA then Sumit’s code is the winner. However, if you have VBAphobia then consider my formula solution. See sheet ‘Pros and Cons’ in the Excel file. Are you one of those rare individuals that is equally competent with VBA and formulas?
Download The Excel File
Download here or via my OneDrive (file ‘Excel or VBA_Create Military Code‘)
How Does The VBA Code Work?
I added comments (green text) to Sumit’s code. You can also read the code in the Excel file above.
How Does The Formula Solution Work?
I tried three different solutions that each used a combination of formulas. The basic idea goes like this:
- MID function extracts each single letter
- MATCH function looks for letter in column A.
- OFFSET (or INDEX) retrieves the word answer from column B.
- Put all words together using CONCATENATE function or &
Sheet ‘Military Alphabet Code (My Fav)‘ is my favorite solution because it’s the easiest to explain.
Linguistic Segue!
As far as I know, military code is meant to work with only one word at a time. My formula solution requires a helper column for each letter. How many letters are in most words?
I found this interesting article about word length in different languages. In English, most words are less than 19 characters long. The words were taken from a dictionary to create chart in Figure 1.
During most conversations we tend to use shorter words. So, if we use all the words from a sample of days (including repeated words) what would the chart look like then? I love Excel and I’m interested in languages but I’m not going to track every word I say for the next week! I’m sure there are linguists who would know the answer.
About Sumit Bansal (Trump Excel)
Sumit Bansal has an MBA (Finance) and worked as a Financial Analyst for several years. Sumit started blogging about Excel in 2013 and hasn’t slept since! He has created a ton of blog posts and articles teaching Excel to the world. You can email him at sumitbansal@trumpexcel.com
Consider taking one of Sumit’s courses:
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst specializing in Microsoft Excel. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish and playing Chess.
Note: I am an affiliate for Sumit’s products and earn a commission on the sales.
I will go with VBA
Nice article Kevin.
It works great with formulas. Especially, if you hide the helper columns, it would be the same as VBA.
Here is another formula that could work:
=IFERROR(INDEX($B$2:$B$27,MATCH(MID($D2,COLUMNS($H$1:H1),1),$A$2:$A$27,0)),””)&IF(I2=””,””,”, “)
It doesn’t use the volatile OFFSET function, and also takes care of the comma between words.