This should be a simple replace...

[greegan]

From columns A:Y I have fields filled in a letter (ie, "A") or letter and number (ie, Y1) or up to three letters with or without a single numeric digit (ie, "ABC" or "ABC1")

I have named each cell and given them a description of what they represent. These letters and numbers are used in tax formulas that I'm not able to change. So I have to compromise here a bit.

I cannot reference them by "Y1" in the formula (obstacle: Y1 could actually be located in cell Y2 and "Y" is in cell Y1) I want to adjust the formula on my end. I want to replace "A" with "Alpha", "Y" with "Yankee" and so on (NATO phonetics with some personal adjustments).

My problem is, when I change cells in column E to include "Echo", and if its a combo of "YE1" for example, then I end up with "YankeEcho1".
I'm going to provide the phonetics I will be using below as I would imagine someone would ask for them if I don't

A,Alpha
A1,Alpha1
B,Beta
B1,Beta1
C,Charlie
D,Delta
D1,Delta1
E,Echo
EI,EchoIndigo
F,Foxtrot
F1,Foxtrot1
F2,Foxtrot2
F3,Foxtrot3
F4,Foxtrot4
G,Gamma
HD,HurricaneDelta
I,Indigo
I1,Indigo1
IE,IndigoEcho
K,Kilo
K1,Kilo1
K1P,Kilo1Papa
K2,Kilo2
K2P,Kilo2Papa
K2Q,Kilo2Quincy
K3,Kilo3
K3P,Kilo3Papa
K4,Kilo4
K4P,Kilo4Papa
KP,KiloPapa
L,Lima
LCF,LimaCharlieFoxtrot
LCP,LimaCharliePapa
M,Mike
M1,Mike1
P,Papa
PI,PapaI
PR,PapaRoger
R,Roger
S,Sam
S1,Sam1
T,Tango
T1,Tango1
T2,Tango2
T3,Tango3
T4,Tango4
TB,TangoB
TC,TangoCharlie
TCP,TangoCharliePapa
U1,Uniform1
V,Victor
V1,Victor1
V2,Victor2
Y,Yankee
YTD,YankeeTangoDelta


Thank you for any assistance you can provide here.

g


PS
Please don't offer criticism regarding the phonetics. I know they are not official codes.

 

[sijpie]

You have explaiend the layout of your worksheet well, but havn't explained your problem very well.

I am assuming that you will get a code from somewhere, say AEG5 and then want to create AlphaEchoGamma5 from that combination.
Is that correct?

 

[Philosophaie]

First you need to just enter the letters in the alphabet individually and their call signs. Then in a cell enter the total phonetics (ie A1T2). From that disect the phonetic by using the Left, Mid and Right Commands. Once you have the individual items separated use a command like "isnumeric" to find the alphabet items and search for the correct Alphabetical item.

A-Z in column 1 rows 5-30
A
B
C
D
to
Z
call signs column 2 rows 5-30
Alpha
Beta
Charlie
Delta
to
Zulu

Do not input any 1s,2s etc or combonational alphanumeric items into rows 5-30!

C

Sub mem()
    Dim a(26), mem(26), pho, first, second, third, fourth, Phonetic As String
    With Sheets("Sheet1")
    For k = 1 To 26
        a(k) = .Cells(k + 4, 1)
        mem(k) = .Cells(k + 4, 2)
    Next k
    pho = .Cells(2, 2)
    first = Left(pho, 1) 'takes the first character "A"
    second = Right(Left(pho, 2), 1)
    If IsNumeric(second) Then
        third = Right(Left(pho, 3), 1)
        fourth = Right(pho, 1)
        If fourth = third Then fourth = ""
        For k = 1 To 26
            If a(k) = first Then print1 = mem(k)
            print2 = second
            If a(k) = third Then print3 = mem(k)
            print4 = fourth
        Next k
    Else
        If second = Right(phon, 1) Then
            third = ""
        Else
            third = Right(phon, 1)
        End If
        For k = 1 To 26
            If a(k) = first Then print1 = mem(k)
            If a(k) = second Then print2 = mem(k)
            print3 = third
            print4 = ""
        Next k
    End If
    Phonetic = print1 & print2 & print3 & print4
    .Cells(2, 4) = Phonetic
    End With
End Sub

 

[Philosophaie]

        If second = Right(phon, 1) Then
            third = ""
        Else
            third = Right(phon, 1)
        End If

should be

       If second = Right(pho, 1) Then
            third = ""
        Else
            third = Right(pho, 1)
        End If

 

[greegan]

WOW! Hey thank you all for the assistance. I will try these out. Thank you very much.


g