military code in excel

[apgmin]

Suppose in cell A1 I have a 8 character set of random alpha numeric characters ( Example say A01XD52SFG)

Then in cell B2 I wish to get the result based on the following ( So the result will be : ALPHA ZERO ONE X-RAY DELTA FIVE TWO SIERRA FOXTROT GOLF )

A	Alfa
B	Bravo
C	Charlie
D	Delta
E	Echo
F	Foxtrot
G	Golf
H	Hotel
I	India
J	Juliett
K	Kilo
L	Lima
M	Mike
N	November
O	Oscar
P	Papa
Q	Quebec
R	Romeo
S	Sierra
T	Tango
U	Uniform
V	Victor
W	Whiskey
X	X-Ray
Y	Yankee
Z	Zulu
1	One
2	Two
3	Three
4	Four
5	five
6	Six
7	Seven
8	Eight
9	Nine
0	Zero

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Your code runs a little better than twice as fast as mine does (10,000 cells... 3 seconds for your function, 7 seconds for mine... so both take less than one-thousandth of a second to execute), so if the OP is processing lots of individual cells, your function is the way to go, otherwise it probably would not matter which function is used.

NOTE
----------
Your function outputs a space at the end of every text string it returns where as mine doesn't. I'm sure the effect will be minimal, but your function will slow down slightly if you choose to add code to remove that trailing space from your results.

It just occurred to me... the times I posted are not purely times for the function to execute, some of that time involves VBA posting a result to a cell... that time would be constant for both our functions and, theoretically, 10,000 of them should be subtracted from what I posted... however, I am not sure how to calculate it. Here is the code I used which may make what I just said clearer...

Sub Test()
  Dim X As Long
  Debug.Print "? " & Timer & " - ";
  For X = 1 To 10000
    Cells(X, "J").Value = Phon(Cells(X, "A").Value)
  Next
  Debug.Print Timer
End Sub

I still would expect your function to be faster than mine, but not by as much as the times I posted would indicate.

 

[shg]

Using this layout with 100 formulas in each column ...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Input​	Aph​	​	Aph2​	​	Military​	​	Military2​	​	Phon​
2​	5I46	five India Four Six		five India Four Six		Five India Four Six		Five India Four Six		Five India Four Six
3​	F4IMT26	Foxtrot Four India Mike Tango Two Six		Foxtrot Four India Mike Tango Two Six		Foxtrot Four India Mike Tango Two Six		Foxtrot Four India Mike Tango Two Six		Foxtrot Four India Mike Tango Two Six
4​	764859WC	Seven Six Four Eight five Nine Whiskey Charlie		Seven Six Four Eight five Nine Whiskey Charlie		Seven Six Four Eight Five Nine Whiskey Charlie		Seven Six Four Eight Five Nine Whiskey Charlie		Seven Six Four Eight Five Nine Whiskey Charlie
5​	1B9430H	One Bravo Nine Four Three Zero Hotel		One Bravo Nine Four Three Zero Hotel		One Bravo Nine Four Three Zero Hotel		One Bravo Nine Four Three Zero Hotel		One Bravo Nine Four Three Zero Hotel
6​	G478749	Golf Four Seven Eight Seven Four Nine		Golf Four Seven Eight Seven Four Nine		Golf Four Seven Eight Seven Four Nine		Golf Four Seven Eight Seven Four Nine		Golf Four Seven Eight Seven Four Nine
7​	99244	Nine Nine Two Four Four		Nine Nine Two Four Four		Nine Nine Two Four Four		Nine Nine Two Four Four		Nine Nine Two Four Four
8​	884	Eight Eight Four		Eight Eight Four		Eight Eight Four		Eight Eight Four		Eight Eight Four

... i get these results:

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

[td]

Time/Calc​

[/td][td]

RelSpeed​

[/td]
[tr][td]B2:B101[/td][td] =Aph(A2)[/td][td]

100​

[/td][td]

32​

[/td][td]

3,200​

[/td][td]

3.754​

[/td][td]

0.001 173 096​

[/td][td]

1.0​

[/td][/tr]
[tr][td]D2:D101[/td][td] =AphRevA(A2)[/td][td]

100​

[/td][td]

256​

[/td][td]

25,600​

[/td][td]

2.352​

[/td][td]

0.000 091 858​

[/td][td]

12.8​

[/td][/tr]
[tr][td]F2:F101[/td][td] =military(A2)[/td][td]

100​

[/td][td]

256​

[/td][td]

25,600​

[/td][td]

4.020​

[/td][td]

0.000 157 013​

[/td][td]

7.5​

[/td][/tr]
[tr][td]H2:H101[/td][td] =Military2(A2)[/td][td]

100​

[/td][td]

128​

[/td][td]

12,800​

[/td][td]

2.254​

[/td][td]

0.000 176 086​

[/td][td]

6.7​

[/td][/tr]
[tr][td]J2:J101[/td][td] =Phon(A2)[/td][td]

100​

[/td][td]

512​

[/td][td]

51,200​

[/td][td]

2.082​

[/td][td]

0.000 040 665​

[/td][td]

28.8​

[/td][/tr]

AphRevA is Mick's code modified to use static variables.

 

[Rick Rothstein]

... i get these results:

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

[td]

Time/Calc​

[/td][td]

RelSpeed​

[/td]
[tr][td]B2:B101[/td][td] =Aph(A2)[/td][td]

100​

[/td][td]

32​

[/td][td]

3,200​

[/td][td]

3.754​

[/td][td]

0.001 173 096​

[/td][td]

1.0​

[/td][/tr]
[tr][td]D2:D101[/td][td] =AphRevA(A2)[/td][td]

100​

[/td][td]

256​

[/td][td]

25,600​

[/td][td]

2.352​

[/td][td]

0.000 091 858​

[/td][td]

12.8​

[/td][/tr]
[tr][td]F2:F101[/td][td] =military(A2)[/td][td]

100​

[/td][td]

256​

[/td][td]

25,600​

[/td][td]

4.020​

[/td][td]

0.000 157 013​

[/td][td]

7.5​

[/td][/tr]
[tr][td]H2:H101[/td][td] =Military2(A2)[/td][td]

100​

[/td][td]

128​

[/td][td]

12,800​

[/td][td]

2.254​

[/td][td]

0.000 176 086​

[/td][td]

6.7​

[/td][/tr]
[tr][td]J2:J101[/td][td] =Phon(A2)[/td][td]

100​

[/td][td]

512​

[/td][td]

51,200​

[/td][td]

2.082​

[/td][td]

0.000 040 665​

[/td][td]

28.8​

[/td][/tr]

Ahh, yes, of course... take out the constant time I talked about in Message #11 and that means the remainder for your function is smaller than the remainder for mine, not more. So your function is 4 times faster than mine, not the 2 times I estimated. What did you use to time these? Also, I'm note sure if you saw my "NOTE" in Message #10 or not (I added it late and you might have missed it)... not that it would make much of a difference, but did you change your code to remove the trailing space for the times you reported in your tests?

 

[shg]

Removing the trailing space:

Function Phon(sInp As String) As String
  ' shg 2015
  ' Returns the NATO phonetic aphabet for the characters in sInp, e.g.,
  ' =Phon("Hi, guys") returns "Hotel India Golf Uniform Yankee Sierra "

  Static bInit      As Boolean
  Const sPhon       As String = "||||||||||||||||||||||||||||||||||||||||||||||||" & _
        "Zero |One |Two |Three |Four |Five |Six |Seven |Eight |Nine ||||||||" & _
        "Alfa |Bravo |Charlie |Delta |Echo |Foxtrot |Golf |Hotel |India |Juliett |Kilo |Lima |Mike |November |Oscar |Papa |Quebec |Romeo |Sierra |Tango |Uniform |Victor |Whiskey |X-Ray |Yankee |Zulu |||||||" & _
        "Alfa |Bravo |Charlie |Delta |Echo |Foxtrot |Golf |Hotel |India |Juliett |Kilo |Lima |Mike |November |Oscar |Papa |Quebec |Romeo |Sierra |Tango |Uniform |Victor |Whiskey |X-Ray |Yankee |Zulu |||||||" & _
        "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
  Static asPhon()   As String
  Dim i             As Long

  If Not bInit Then
    asPhon = Split(sPhon, "|")
    bInit = True
  End If

  For i = 1& To Len(sInp)
    Phon = Phon & asPhon(Asc(Mid$(sInp, i, 1&)))
  Next i
  Phon = Trim$(Phon)
End Function

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

[td]

Time/Calc​

[/td][td]

RelSpeed​

[/td]
[tr][td]H2:H101[/td][td] =Military2(A2)[/td][td]

100​

[/td][td]

128​

[/td][td]

12,800​

[/td][td]

2.316​

[/td][td]

0.000 180 969​

[/td][td]

1.0​

[/td][/tr]
[tr][td]F2:F101[/td][td] =military(A2)[/td][td]

100​

[/td][td]

128​

[/td][td]

12,800​

[/td][td]

2.059​

[/td][td]

0.000 160 828​

[/td][td]

1.1​

[/td][/tr]
[tr][td]J2:J101[/td][td] =Phon(A2)[/td][td]

100​

[/td][td]

512​

[/td][td]

51,200​

[/td][td]

2.262​

[/td][td]

0.000 044 174​

[/td][td]

4.1​

[/td][/tr]

What did you use to time these?

The formula timer workbook at https://app.box.com/s/b5ykwwa9x6xs8d7v9vhv

 

[navic]

spelling code in excel

did it by =MID($D7,1,1)
=VLOOKUP(E7, A2:B37, 2, FALSE)
=CONCATENATE(M7," ",N7," ",O7," ",P7," ",Q7," ",R7," ",S7," ",T7)

A different way of using only one long formula in a cell
The formula copied from Excel blog for beginners

=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),"")