Assignmentgeek.com - makes your homework shine. # Military or Radio Amateur Code Spelling in Excel

## The spelling radio amateur code - Military code in Excel 2013

Excel can be used for automatic spelling Military or Radio Amateur Code. I believe that you are in a lot of military cinema films noticed that certain characters (letters and numbers) pronounce certain words. Military codes have specific meanings in each country, amateur radio at the spelling has international significance, and I believe that each country has its word to identify certain letters in certain branches (ham radio, international labels airplanes, etc.)

I will here an Excel example of the letters/numerals and their equivalents words. So using the formula we can read the code and automatically print all the words that belong to a particular code.

You notice in the picture below it. Column 'A' are the letters & numbers, in column 'B' are related words for each code. Of course to amateur radio these words may appear different. Column 'D' are codes (10 digits) in the column 'F' is the formula that reads the code in column 'D' and returns a list of words to read at spelling.

If we look a little content of the column 'A', we can see that it contains letters and numbers. If this solves the task of the VLOOKUP formula or INDEX/MATCH nested formula problems occur because we have letters/numbers. Of course, this task can be solved using auxiliary columns or cells, as well as VBA/UDF function.

My goal is to solve the task without the use of additional columns/cells and also without VBA/UDF code.

The task is solved by the following formula in cell 'F1' which is copied below. (formulas should be on one line). Pay attention to the settings of your Excel. You may need to use a comma (,) instead of a semicolon (;)

=VLOOKUP(IFERROR(ABS(MID(\$D1;1;1));MID(\$D1;1;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;2;1));MID(\$D1;2;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;3;1));MID(\$D1;3;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;4;1));MID(\$D1;4;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;5;1));MID(\$D1;5;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;6;1));MID(\$D1;6;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;7;1));MID(\$D1;7;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;8;1));MID(\$D1;8;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;9;1));MID(\$D1;9;1));\$A\$1:\$B\$36;2;FALSE)&" "&VLOOKUP(IFERROR(ABS(MID(\$D1;10;1));MID(\$D1;10;1));\$A\$1:\$B\$36;2;FALSE)

Do not be afraid of the long formula. It is in fact a formula multiplied 10 times because we have ten characters in a cell 'D1'. This formula we could use in combination with CONCATENATE function.

So the basic formula is:
=VLOOKUP(IFERROR(ABS(MID(\$D1;1;1));MID(\$D1;1;1));\$A\$1:\$B\$36;2;FALSE)

This formula returns for each character, which is a requirement set out in column 'A' equivalent word in column 'B'. This formula again consists of several functions that are nested. I will try to explain how it works this basic formula.

You notice MID function that is nested within Excel functions like ABS function and within IFERROR Excel functions.

Excel MID function has the syntax:
=MID(text;start_num;num_chars)

So this function searches for the cell with the text; starting with a specific character; 'returns a certain number of characters, considering to the cell 'D1' are numbers and letters, there is a problem encountered when the formula number. In fact, this formula will properly return the character we're looking for but will create a complication function VLOOKUP when the number appears. Then the formula with a VLOOKUP function returned error #N/A.

If we look only at first character the 'D1' (the letter A), then the formula returns the correct result

=VLOOKUP(MID(\$D\$1;1;1);\$A\$1:\$B\$36;2;FALSE)

If we look at the second character in the 'D1' (number 0), then this formula returns a #N/A

=VLOOKUP(MID(\$D\$1;1;1);\$A\$1:\$B\$36;2;FALSE)

In order to we prevent this error #N/A, we will use other Excel ABS function in which we nestle our MID function. So far the formula looks like this below.

=ABS(MID(\$D1;1;1)) => for numbers

This formula will return the correct result when a sign of a number, but will return the error #VALUE! if the respective character text or letter. So again we have a problem. But to avoid this problem, we use Excel IFERROR function.

So we have two kinds of formula.

=ABS(MID(\$D1;1;1)) => for numbers

=MID(\$D1;1;1) => for letters

I would not go far in depth explanation of the function of the ABS/MID. Suffice it to say that there are two states, error/result.

First, to explain the syntax IFERROR functions.

=IFERROR(formula;"")

So our ABS/MID formula nest inside IFERROR functions and if the result is OK then return to the respective result, and if it is not OK then return empty cell. Considering that we have two kinds of characters (letters / numbers) and if you use one type of formula for the first type of characters, then we have a problem with another formula to other types of characters. To avoid this problem, in the IFERROR function, we will be able nestle both types of formulas, and so following formula.

=VLOOKUP(IFERROR(ABS(MID(\$D1;1;1));MID(\$D1;1;1));\$A\$1:\$B\$36;2;FALSE)

I hope you understand the rest of VLOOKUP formula.

And finally we have the result spelling words for each particular character. See cell 'F1' in the picture above.

What is the basic problem with the VLOOKUP formula in this case. The problem is that we have the letters and numbers. MID function returns all results as a text, but a problem arises when there is a sign "number" and the formula it is treated as text. To understand this, try this VLOOKUP formula themselves and look across Evaluate calculation formulas.

## The spelling of numbers and letters - setting Formula in the Name Manager

This task can be solved in a different way by named Formula. In the Name Manager put Ten (10) formula which is called by different names. So the Formulas tab, click Name Manager command.

• In the Name: spelling1 (spelling2, spelling3 etc.)
• In the field Scope: Sheet1
• In the Refers to: set a formula for each name For each name formula spelling1, spelling2 ... spelling10 set up the appropriate formula

=VLOOKUP(IFERROR(ABS(MID(\$F\$1;1;1));MID(\$F\$1;1;1));\$A\$1:\$B\$36;2;FALSE)
=VLOOKUP(IFERROR(ABS(MID(\$F\$1;2;1));MID(\$F\$1;2;1));\$A\$1:\$B\$36;2;FALSE)
=VLOOKUP(IFERROR(ABS(MID(\$F\$1;3;1));MID(\$F\$1;3;1));\$A\$1:\$B\$36;2;FALSE)
...
...
=VLOOKUP(IFERROR(ABS(MID(\$F\$1;10;1));MID(\$F\$1;10;1));\$A\$1:\$B\$36;2;FALSE)

And finally in a cell 'F1' set the formula (remember, these items are Named Formula, this formula below, copy down)

=CONCATENATE(spelling1;" "; spelling2;" ";spelling3;" ";spelling4;" ";spelling5;" ";spelling6;" ";spelling7;" ";spelling8;" ";spelling9;" ";spelling10;" ";)

## The spelling sentences or phrases in Excel using a formula

If you want to spell a sentence or phrase that contains additional characters such as a comma, point, blank character, etc., in a range of cells, add the respective signs and the corresponding words and formulas expand the range of cells \$A\$1:\$B\$39.

In this case up to 16 characters formula is changing and the formula looks like this (formula must be a single line of code)

=IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;1;1));MID(\$F1;1;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;2;1));MID(\$F1;2;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;3;1));MID(\$F1;3;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;4;1));MID(\$F1;4;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;5;1));MID(\$F1;5;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;6;1));MID(\$F1;6;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;7;1));MID(\$F1;7;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;8;1));MID(\$F1;8;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;9;1));MID(\$F1;9;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;10;1));MID(\$F1;10;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;11;1));MID(\$F1;11;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;12;1));MID(\$F1;12;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;13;1));MID(\$F1;13;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;14;1));MID(\$F1;14;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;15;1));MID(\$F1;15;1));\$A\$1:\$B\$39;2;FALSE);"")&" "&IFERROR(VLOOKUP(IFERROR(ABS(MID(\$F1;16;1));MID(\$F1;16;1));\$A\$1:\$B\$39;2;FALSE);"")

You can copy the formula down and to have the result of spelling sentence in column D.

If you have a lot of rows in column 'D', double-click the "fill handle" to automatically copy the formula to the last row.

And finally we have achieved this long formula at the beginning of the tutorial. We automatically read the text at in cell 'D1' and as a result returned all related words.