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>

 

[MickG]

Try this UDF
If your example is in "B1" then in "B2":- =aph(B1)

Function Aph(St As String) As String
Dim Ray As Variant, n As Long, Str As String
Ray = Array("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")

With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    For n = 0 To UBound(Ray) Step 2
       .Item(Ray(n)) = Ray(n + 1)
    Next
For n = 1 To Len(St)
       Str = Str & " " & .Item(Mid(St, n, 1))
Next n
End With
Aph = Mid(Str, 2)
End Function

 

[apgmin]

thanks

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

<tbody>
</tbody>

 

[apgmin]

this also works thanks

 

[njimack]

ADVERTISEMENT

thanks

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

<tbody>
</tbody>

MID($D7,1,1) could be simplified to LEFT($D7)

 

[Rick Rothstein]

I see you went with a table/formula approach, which is fine, of course, but if you ever wanted to be able to do away with the table, here is another UDF you could consider using (and the function could also be called by other VB code if needed)...

Function Military(S As String) As String
  Dim X As Long
  Const N As String = "AAlfa BBravo CCharlie DDelta EEcho FFoxtrot " & _
                      "GGolf HHotel IIndia JJuliett KKilo LLima MMike " & _
                      "NNovember OOscar PPapa QQuebec RRomeo SSierra TTango " & _
                      "UUniform VVictor WWhiskey XX-Ray YYankee ZZulu " & _
                      "1One 2Two 3Three 4Four 5Five 6Six 7Seven 8Eight 9Nine 0Zero"
  For X = 1 To Len(S)
    Military = Military & " " & Split(Split(N, Mid(S, X, 1), 2, vbBinaryCompare)(1))(0)
  Next
  Military = Trim(Military)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Military just like it was a built-in Excel function. For example,

=Military(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[shg]

ADVERTISEMENT

Another way:

Function Phon(sInp As String) As String
  Static bInit      As Boolean
  Const sPh         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 asPh()     As String
  Dim i             As Long

  If Not bInit Then
    asPh = Split(sPh, "|")
    bInit = True
  End If

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

Row\Col	A​	B​	C​
1​	Hi, guys	Hotel India Golf Uniform Yankee Sierra	B1:=Phon(A1)

 

[Rick Rothstein]

Row\Col	A​	B​	C​
1​	Hi, guys	Hotel India Golf Uniform Yankee Sierra	B1:=Phon(A1)

On the off-chance you are right, and the OP's text could contain spaces, punctuation characters and lowercase letters, here is my code modified to deal with them...

[SIZE=1]Function Military(S As String) As String
  Dim X As Long
  Const N As String = "AAlfa BBravo CCharlie DDelta EEcho FFoxtrot GGolf HHotel IIndia " & _
                      "JJuliett KKilo LLima MMike NNovember OOscar PPapa QQuebec RRomeo " & _
                      "SSierra TTango UUniform VVictor WWhiskey XX-Ray YYankee ZZulu " & _
                      "1One 2Two 3Three 4Four 5Five 6Six 7Seven 8Eight 9Nine 0Zero"
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Za-z0-9]" Then
      Military = Military & " " & Split(Split(N, Mid(UCase(S), X, 1), 2, vbBinaryCompare)(1))(0)
    End If
  Next
  Military = Trim(Military)
End Function[/SIZE]

 

[shg]

Speed comparison?

 

[Rick Rothstein]

Speed comparison?

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.