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

Excel can be used for

I will here an example of the

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.

**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 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 formula VLOOKUP or INDEX/MATCH 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

=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 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 function VLOOKUP returned error

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

=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 function ABS 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 function IFERROR.

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 functions IFERROR.

=IFERROR(formula;"")

So our

=VLOOKUP(

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 formula VLOOKUP themselves and look across Evaluate calculation formulas.

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

=VLOOKUP(IFERROR(ABS(MID($F$1;

=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;

And finally in a cell 'F1' set the formula (this formula below, copy down)

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

In this case up to

=IFERROR(VLOOKUP(IFERROR(ABS(MID($F1;

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',

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.

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 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 function VLOOKUP 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 function ABS 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 function IFERROR.

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 functions IFERROR.

=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 function IFERROR, 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 formula VLOOKUP 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. In the Name Manager put ten formula which is called by different names. So the Formulas tab, click**Name Manager**commandIn 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 (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$39In 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.